sqlglot.generator

   1from __future__ import annotations
   2
   3import logging
   4import re
   5import typing as t
   6from collections import defaultdict
   7from functools import reduce, wraps
   8
   9from sqlglot import exp
  10from sqlglot.errors import ErrorLevel, UnsupportedError, concat_messages
  11from sqlglot.helper import apply_index_offset, csv, name_sequence, seq_get
  12from sqlglot.jsonpath import ALL_JSON_PATH_PARTS, JSON_PATH_PART_TRANSFORMS
  13from sqlglot.time import format_time
  14from sqlglot.tokens import TokenType
  15
  16if t.TYPE_CHECKING:
  17    from sqlglot._typing import E
  18    from sqlglot.dialects.dialect import DialectType
  19
  20    G = t.TypeVar("G", bound="Generator")
  21    GeneratorMethod = t.Callable[[G, E], str]
  22
  23logger = logging.getLogger("sqlglot")
  24
  25ESCAPED_UNICODE_RE = re.compile(r"\\(\d+)")
  26UNSUPPORTED_TEMPLATE = "Argument '{}' is not supported for expression '{}' when targeting {}."
  27
  28
  29def unsupported_args(
  30    *args: t.Union[str, t.Tuple[str, str]],
  31) -> t.Callable[[GeneratorMethod], GeneratorMethod]:
  32    """
  33    Decorator that can be used to mark certain args of an `Expression` subclass as unsupported.
  34    It expects a sequence of argument names or pairs of the form (argument_name, diagnostic_msg).
  35    """
  36    diagnostic_by_arg: t.Dict[str, t.Optional[str]] = {}
  37    for arg in args:
  38        if isinstance(arg, str):
  39            diagnostic_by_arg[arg] = None
  40        else:
  41            diagnostic_by_arg[arg[0]] = arg[1]
  42
  43    def decorator(func: GeneratorMethod) -> GeneratorMethod:
  44        @wraps(func)
  45        def _func(generator: G, expression: E) -> str:
  46            expression_name = expression.__class__.__name__
  47            dialect_name = generator.dialect.__class__.__name__
  48
  49            for arg_name, diagnostic in diagnostic_by_arg.items():
  50                if expression.args.get(arg_name):
  51                    diagnostic = diagnostic or UNSUPPORTED_TEMPLATE.format(
  52                        arg_name, expression_name, dialect_name
  53                    )
  54                    generator.unsupported(diagnostic)
  55
  56            return func(generator, expression)
  57
  58        return _func
  59
  60    return decorator
  61
  62
  63class _Generator(type):
  64    def __new__(cls, clsname, bases, attrs):
  65        klass = super().__new__(cls, clsname, bases, attrs)
  66
  67        # Remove transforms that correspond to unsupported JSONPathPart expressions
  68        for part in ALL_JSON_PATH_PARTS - klass.SUPPORTED_JSON_PATH_PARTS:
  69            klass.TRANSFORMS.pop(part, None)
  70
  71        return klass
  72
  73
  74class Generator(metaclass=_Generator):
  75    """
  76    Generator converts a given syntax tree to the corresponding SQL string.
  77
  78    Args:
  79        pretty: Whether to format the produced SQL string.
  80            Default: False.
  81        identify: Determines when an identifier should be quoted. Possible values are:
  82            False (default): Never quote, except in cases where it's mandatory by the dialect.
  83            True or 'always': Always quote.
  84            'safe': Only quote identifiers that are case insensitive.
  85        normalize: Whether to normalize identifiers to lowercase.
  86            Default: False.
  87        pad: The pad size in a formatted string. For example, this affects the indentation of
  88            a projection in a query, relative to its nesting level.
  89            Default: 2.
  90        indent: The indentation size in a formatted string. For example, this affects the
  91            indentation of subqueries and filters under a `WHERE` clause.
  92            Default: 2.
  93        normalize_functions: How to normalize function names. Possible values are:
  94            "upper" or True (default): Convert names to uppercase.
  95            "lower": Convert names to lowercase.
  96            False: Disables function name normalization.
  97        unsupported_level: Determines the generator's behavior when it encounters unsupported expressions.
  98            Default ErrorLevel.WARN.
  99        max_unsupported: Maximum number of unsupported messages to include in a raised UnsupportedError.
 100            This is only relevant if unsupported_level is ErrorLevel.RAISE.
 101            Default: 3
 102        leading_comma: Whether the comma is leading or trailing in select expressions.
 103            This is only relevant when generating in pretty mode.
 104            Default: False
 105        max_text_width: The max number of characters in a segment before creating new lines in pretty mode.
 106            The default is on the smaller end because the length only represents a segment and not the true
 107            line length.
 108            Default: 80
 109        comments: Whether to preserve comments in the output SQL code.
 110            Default: True
 111    """
 112
 113    TRANSFORMS: t.Dict[t.Type[exp.Expression], t.Callable[..., str]] = {
 114        **JSON_PATH_PART_TRANSFORMS,
 115        exp.AllowedValuesProperty: lambda self,
 116        e: f"ALLOWED_VALUES {self.expressions(e, flat=True)}",
 117        exp.AnalyzeColumns: lambda self, e: self.sql(e, "this"),
 118        exp.AnalyzeWith: lambda self, e: self.expressions(e, prefix="WITH ", sep=" "),
 119        exp.ArrayContainsAll: lambda self, e: self.binary(e, "@>"),
 120        exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
 121        exp.AutoRefreshProperty: lambda self, e: f"AUTO REFRESH {self.sql(e, 'this')}",
 122        exp.BackupProperty: lambda self, e: f"BACKUP {self.sql(e, 'this')}",
 123        exp.CaseSpecificColumnConstraint: lambda _,
 124        e: f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC",
 125        exp.Ceil: lambda self, e: self.ceil_floor(e),
 126        exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}",
 127        exp.CharacterSetProperty: lambda self,
 128        e: f"{'DEFAULT ' if e.args.get('default') else ''}CHARACTER SET={self.sql(e, 'this')}",
 129        exp.ClusteredColumnConstraint: lambda self,
 130        e: f"CLUSTERED ({self.expressions(e, 'this', indent=False)})",
 131        exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}",
 132        exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}",
 133        exp.ConnectByRoot: lambda self, e: f"CONNECT_BY_ROOT {self.sql(e, 'this')}",
 134        exp.ConvertToCharset: lambda self, e: self.func(
 135            "CONVERT", e.this, e.args["dest"], e.args.get("source")
 136        ),
 137        exp.CopyGrantsProperty: lambda *_: "COPY GRANTS",
 138        exp.CredentialsProperty: lambda self,
 139        e: f"CREDENTIALS=({self.expressions(e, 'expressions', sep=' ')})",
 140        exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}",
 141        exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}",
 142        exp.DynamicProperty: lambda *_: "DYNAMIC",
 143        exp.EmptyProperty: lambda *_: "EMPTY",
 144        exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}",
 145        exp.EnviromentProperty: lambda self, e: f"ENVIRONMENT ({self.expressions(e, flat=True)})",
 146        exp.EphemeralColumnConstraint: lambda self,
 147        e: f"EPHEMERAL{(' ' + self.sql(e, 'this')) if e.this else ''}",
 148        exp.ExcludeColumnConstraint: lambda self, e: f"EXCLUDE {self.sql(e, 'this').lstrip()}",
 149        exp.ExecuteAsProperty: lambda self, e: self.naked_property(e),
 150        exp.Except: lambda self, e: self.set_operations(e),
 151        exp.ExternalProperty: lambda *_: "EXTERNAL",
 152        exp.Floor: lambda self, e: self.ceil_floor(e),
 153        exp.Get: lambda self, e: self.get_put_sql(e),
 154        exp.GlobalProperty: lambda *_: "GLOBAL",
 155        exp.HeapProperty: lambda *_: "HEAP",
 156        exp.IcebergProperty: lambda *_: "ICEBERG",
 157        exp.InheritsProperty: lambda self, e: f"INHERITS ({self.expressions(e, flat=True)})",
 158        exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}",
 159        exp.InputModelProperty: lambda self, e: f"INPUT{self.sql(e, 'this')}",
 160        exp.Intersect: lambda self, e: self.set_operations(e),
 161        exp.IntervalSpan: lambda self, e: f"{self.sql(e, 'this')} TO {self.sql(e, 'expression')}",
 162        exp.Int64: lambda self, e: self.sql(exp.cast(e.this, exp.DataType.Type.BIGINT)),
 163        exp.LanguageProperty: lambda self, e: self.naked_property(e),
 164        exp.LocationProperty: lambda self, e: self.naked_property(e),
 165        exp.LogProperty: lambda _, e: f"{'NO ' if e.args.get('no') else ''}LOG",
 166        exp.MaterializedProperty: lambda *_: "MATERIALIZED",
 167        exp.NonClusteredColumnConstraint: lambda self,
 168        e: f"NONCLUSTERED ({self.expressions(e, 'this', indent=False)})",
 169        exp.NoPrimaryIndexProperty: lambda *_: "NO PRIMARY INDEX",
 170        exp.NotForReplicationColumnConstraint: lambda *_: "NOT FOR REPLICATION",
 171        exp.OnCommitProperty: lambda _,
 172        e: f"ON COMMIT {'DELETE' if e.args.get('delete') else 'PRESERVE'} ROWS",
 173        exp.OnProperty: lambda self, e: f"ON {self.sql(e, 'this')}",
 174        exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}",
 175        exp.Operator: lambda self, e: self.binary(e, ""),  # The operator is produced in `binary`
 176        exp.OutputModelProperty: lambda self, e: f"OUTPUT{self.sql(e, 'this')}",
 177        exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}",
 178        exp.PartitionedByBucket: lambda self, e: self.func("BUCKET", e.this, e.expression),
 179        exp.PartitionByTruncate: lambda self, e: self.func("TRUNCATE", e.this, e.expression),
 180        exp.PivotAny: lambda self, e: f"ANY{self.sql(e, 'this')}",
 181        exp.PositionalColumn: lambda self, e: f"#{self.sql(e, 'this')}",
 182        exp.ProjectionPolicyColumnConstraint: lambda self,
 183        e: f"PROJECTION POLICY {self.sql(e, 'this')}",
 184        exp.Put: lambda self, e: self.get_put_sql(e),
 185        exp.RemoteWithConnectionModelProperty: lambda self,
 186        e: f"REMOTE WITH CONNECTION {self.sql(e, 'this')}",
 187        exp.ReturnsProperty: lambda self, e: (
 188            "RETURNS NULL ON NULL INPUT" if e.args.get("null") else self.naked_property(e)
 189        ),
 190        exp.SampleProperty: lambda self, e: f"SAMPLE BY {self.sql(e, 'this')}",
 191        exp.SecureProperty: lambda *_: "SECURE",
 192        exp.SecurityProperty: lambda self, e: f"SECURITY {self.sql(e, 'this')}",
 193        exp.SetConfigProperty: lambda self, e: self.sql(e, "this"),
 194        exp.SetProperty: lambda _, e: f"{'MULTI' if e.args.get('multi') else ''}SET",
 195        exp.SettingsProperty: lambda self, e: f"SETTINGS{self.seg('')}{(self.expressions(e))}",
 196        exp.SharingProperty: lambda self, e: f"SHARING={self.sql(e, 'this')}",
 197        exp.SqlReadWriteProperty: lambda _, e: e.name,
 198        exp.SqlSecurityProperty: lambda _,
 199        e: f"SQL SECURITY {'DEFINER' if e.args.get('definer') else 'INVOKER'}",
 200        exp.StabilityProperty: lambda _, e: e.name,
 201        exp.Stream: lambda self, e: f"STREAM {self.sql(e, 'this')}",
 202        exp.StreamingTableProperty: lambda *_: "STREAMING",
 203        exp.StrictProperty: lambda *_: "STRICT",
 204        exp.SwapTable: lambda self, e: f"SWAP WITH {self.sql(e, 'this')}",
 205        exp.TableColumn: lambda self, e: self.sql(e.this),
 206        exp.Tags: lambda self, e: f"TAG ({self.expressions(e, flat=True)})",
 207        exp.TemporaryProperty: lambda *_: "TEMPORARY",
 208        exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}",
 209        exp.ToMap: lambda self, e: f"MAP {self.sql(e, 'this')}",
 210        exp.ToTableProperty: lambda self, e: f"TO {self.sql(e.this)}",
 211        exp.TransformModelProperty: lambda self, e: self.func("TRANSFORM", *e.expressions),
 212        exp.TransientProperty: lambda *_: "TRANSIENT",
 213        exp.Union: lambda self, e: self.set_operations(e),
 214        exp.UnloggedProperty: lambda *_: "UNLOGGED",
 215        exp.UsingTemplateProperty: lambda self, e: f"USING TEMPLATE {self.sql(e, 'this')}",
 216        exp.UsingData: lambda self, e: f"USING DATA {self.sql(e, 'this')}",
 217        exp.Uuid: lambda *_: "UUID()",
 218        exp.UppercaseColumnConstraint: lambda *_: "UPPERCASE",
 219        exp.UtcDate: lambda self, e: self.sql(exp.CurrentDate(this=exp.Literal.string("UTC"))),
 220        exp.UtcTime: lambda self, e: self.sql(exp.CurrentTime(this=exp.Literal.string("UTC"))),
 221        exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]),
 222        exp.ViewAttributeProperty: lambda self, e: f"WITH {self.sql(e, 'this')}",
 223        exp.VolatileProperty: lambda *_: "VOLATILE",
 224        exp.WeekStart: lambda self, e: f"WEEK({self.sql(e, 'this')})",
 225        exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}",
 226        exp.WithProcedureOptions: lambda self, e: f"WITH {self.expressions(e, flat=True)}",
 227        exp.WithSchemaBindingProperty: lambda self, e: f"WITH SCHEMA {self.sql(e, 'this')}",
 228        exp.WithOperator: lambda self, e: f"{self.sql(e, 'this')} WITH {self.sql(e, 'op')}",
 229        exp.ForceProperty: lambda *_: "FORCE",
 230    }
 231
 232    # Whether null ordering is supported in order by
 233    # True: Full Support, None: No support, False: No support for certain cases
 234    # such as window specifications, aggregate functions etc
 235    NULL_ORDERING_SUPPORTED: t.Optional[bool] = True
 236
 237    # Whether ignore nulls is inside the agg or outside.
 238    # FIRST(x IGNORE NULLS) OVER vs FIRST (x) IGNORE NULLS OVER
 239    IGNORE_NULLS_IN_FUNC = False
 240
 241    # Whether locking reads (i.e. SELECT ... FOR UPDATE/SHARE) are supported
 242    LOCKING_READS_SUPPORTED = False
 243
 244    # Whether the EXCEPT and INTERSECT operations can return duplicates
 245    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = True
 246
 247    # Wrap derived values in parens, usually standard but spark doesn't support it
 248    WRAP_DERIVED_VALUES = True
 249
 250    # Whether create function uses an AS before the RETURN
 251    CREATE_FUNCTION_RETURN_AS = True
 252
 253    # Whether MERGE ... WHEN MATCHED BY SOURCE is allowed
 254    MATCHED_BY_SOURCE = True
 255
 256    # Whether the INTERVAL expression works only with values like '1 day'
 257    SINGLE_STRING_INTERVAL = False
 258
 259    # Whether the plural form of date parts like day (i.e. "days") is supported in INTERVALs
 260    INTERVAL_ALLOWS_PLURAL_FORM = True
 261
 262    # Whether limit and fetch are supported (possible values: "ALL", "LIMIT", "FETCH")
 263    LIMIT_FETCH = "ALL"
 264
 265    # Whether limit and fetch allows expresions or just limits
 266    LIMIT_ONLY_LITERALS = False
 267
 268    # Whether a table is allowed to be renamed with a db
 269    RENAME_TABLE_WITH_DB = True
 270
 271    # The separator for grouping sets and rollups
 272    GROUPINGS_SEP = ","
 273
 274    # The string used for creating an index on a table
 275    INDEX_ON = "ON"
 276
 277    # Whether join hints should be generated
 278    JOIN_HINTS = True
 279
 280    # Whether table hints should be generated
 281    TABLE_HINTS = True
 282
 283    # Whether query hints should be generated
 284    QUERY_HINTS = True
 285
 286    # What kind of separator to use for query hints
 287    QUERY_HINT_SEP = ", "
 288
 289    # Whether comparing against booleans (e.g. x IS TRUE) is supported
 290    IS_BOOL_ALLOWED = True
 291
 292    # Whether to include the "SET" keyword in the "INSERT ... ON DUPLICATE KEY UPDATE" statement
 293    DUPLICATE_KEY_UPDATE_WITH_SET = True
 294
 295    # Whether to generate the limit as TOP <value> instead of LIMIT <value>
 296    LIMIT_IS_TOP = False
 297
 298    # Whether to generate INSERT INTO ... RETURNING or INSERT INTO RETURNING ...
 299    RETURNING_END = True
 300
 301    # Whether to generate an unquoted value for EXTRACT's date part argument
 302    EXTRACT_ALLOWS_QUOTES = True
 303
 304    # Whether TIMETZ / TIMESTAMPTZ will be generated using the "WITH TIME ZONE" syntax
 305    TZ_TO_WITH_TIME_ZONE = False
 306
 307    # Whether the NVL2 function is supported
 308    NVL2_SUPPORTED = True
 309
 310    # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
 311    SELECT_KINDS: t.Tuple[str, ...] = ("STRUCT", "VALUE")
 312
 313    # Whether VALUES statements can be used as derived tables.
 314    # MySQL 5 and Redshift do not allow this, so when False, it will convert
 315    # SELECT * VALUES into SELECT UNION
 316    VALUES_AS_TABLE = True
 317
 318    # Whether the word COLUMN is included when adding a column with ALTER TABLE
 319    ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = True
 320
 321    # UNNEST WITH ORDINALITY (presto) instead of UNNEST WITH OFFSET (bigquery)
 322    UNNEST_WITH_ORDINALITY = True
 323
 324    # Whether FILTER (WHERE cond) can be used for conditional aggregation
 325    AGGREGATE_FILTER_SUPPORTED = True
 326
 327    # Whether JOIN sides (LEFT, RIGHT) are supported in conjunction with SEMI/ANTI join kinds
 328    SEMI_ANTI_JOIN_WITH_SIDE = True
 329
 330    # Whether to include the type of a computed column in the CREATE DDL
 331    COMPUTED_COLUMN_WITH_TYPE = True
 332
 333    # Whether CREATE TABLE .. COPY .. is supported. False means we'll generate CLONE instead of COPY
 334    SUPPORTS_TABLE_COPY = True
 335
 336    # Whether parentheses are required around the table sample's expression
 337    TABLESAMPLE_REQUIRES_PARENS = True
 338
 339    # Whether a table sample clause's size needs to be followed by the ROWS keyword
 340    TABLESAMPLE_SIZE_IS_ROWS = True
 341
 342    # The keyword(s) to use when generating a sample clause
 343    TABLESAMPLE_KEYWORDS = "TABLESAMPLE"
 344
 345    # Whether the TABLESAMPLE clause supports a method name, like BERNOULLI
 346    TABLESAMPLE_WITH_METHOD = True
 347
 348    # The keyword to use when specifying the seed of a sample clause
 349    TABLESAMPLE_SEED_KEYWORD = "SEED"
 350
 351    # Whether COLLATE is a function instead of a binary operator
 352    COLLATE_IS_FUNC = False
 353
 354    # Whether data types support additional specifiers like e.g. CHAR or BYTE (oracle)
 355    DATA_TYPE_SPECIFIERS_ALLOWED = False
 356
 357    # Whether conditions require booleans WHERE x = 0 vs WHERE x
 358    ENSURE_BOOLS = False
 359
 360    # Whether the "RECURSIVE" keyword is required when defining recursive CTEs
 361    CTE_RECURSIVE_KEYWORD_REQUIRED = True
 362
 363    # Whether CONCAT requires >1 arguments
 364    SUPPORTS_SINGLE_ARG_CONCAT = True
 365
 366    # Whether LAST_DAY function supports a date part argument
 367    LAST_DAY_SUPPORTS_DATE_PART = True
 368
 369    # Whether named columns are allowed in table aliases
 370    SUPPORTS_TABLE_ALIAS_COLUMNS = True
 371
 372    # Whether UNPIVOT aliases are Identifiers (False means they're Literals)
 373    UNPIVOT_ALIASES_ARE_IDENTIFIERS = True
 374
 375    # What delimiter to use for separating JSON key/value pairs
 376    JSON_KEY_VALUE_PAIR_SEP = ":"
 377
 378    # INSERT OVERWRITE TABLE x override
 379    INSERT_OVERWRITE = " OVERWRITE TABLE"
 380
 381    # Whether the SELECT .. INTO syntax is used instead of CTAS
 382    SUPPORTS_SELECT_INTO = False
 383
 384    # Whether UNLOGGED tables can be created
 385    SUPPORTS_UNLOGGED_TABLES = False
 386
 387    # Whether the CREATE TABLE LIKE statement is supported
 388    SUPPORTS_CREATE_TABLE_LIKE = True
 389
 390    # Whether the LikeProperty needs to be specified inside of the schema clause
 391    LIKE_PROPERTY_INSIDE_SCHEMA = False
 392
 393    # Whether DISTINCT can be followed by multiple args in an AggFunc. If not, it will be
 394    # transpiled into a series of CASE-WHEN-ELSE, ultimately using a tuple conseisting of the args
 395    MULTI_ARG_DISTINCT = True
 396
 397    # Whether the JSON extraction operators expect a value of type JSON
 398    JSON_TYPE_REQUIRED_FOR_EXTRACTION = False
 399
 400    # Whether bracketed keys like ["foo"] are supported in JSON paths
 401    JSON_PATH_BRACKETED_KEY_SUPPORTED = True
 402
 403    # Whether to escape keys using single quotes in JSON paths
 404    JSON_PATH_SINGLE_QUOTE_ESCAPE = False
 405
 406    # The JSONPathPart expressions supported by this dialect
 407    SUPPORTED_JSON_PATH_PARTS = ALL_JSON_PATH_PARTS.copy()
 408
 409    # Whether any(f(x) for x in array) can be implemented by this dialect
 410    CAN_IMPLEMENT_ARRAY_ANY = False
 411
 412    # Whether the function TO_NUMBER is supported
 413    SUPPORTS_TO_NUMBER = True
 414
 415    # Whether EXCLUDE in window specification is supported
 416    SUPPORTS_WINDOW_EXCLUDE = False
 417
 418    # Whether or not set op modifiers apply to the outer set op or select.
 419    # SELECT * FROM x UNION SELECT * FROM y LIMIT 1
 420    # True means limit 1 happens after the set op, False means it it happens on y.
 421    SET_OP_MODIFIERS = True
 422
 423    # Whether parameters from COPY statement are wrapped in parentheses
 424    COPY_PARAMS_ARE_WRAPPED = True
 425
 426    # Whether values of params are set with "=" token or empty space
 427    COPY_PARAMS_EQ_REQUIRED = False
 428
 429    # Whether COPY statement has INTO keyword
 430    COPY_HAS_INTO_KEYWORD = True
 431
 432    # Whether the conditional TRY(expression) function is supported
 433    TRY_SUPPORTED = True
 434
 435    # Whether the UESCAPE syntax in unicode strings is supported
 436    SUPPORTS_UESCAPE = True
 437
 438    # Function used to replace escaped unicode codes in unicode strings
 439    UNICODE_SUBSTITUTE: t.Optional[t.Callable[[re.Match[str]], str]] = None
 440
 441    # The keyword to use when generating a star projection with excluded columns
 442    STAR_EXCEPT = "EXCEPT"
 443
 444    # The HEX function name
 445    HEX_FUNC = "HEX"
 446
 447    # The keywords to use when prefixing & separating WITH based properties
 448    WITH_PROPERTIES_PREFIX = "WITH"
 449
 450    # Whether to quote the generated expression of exp.JsonPath
 451    QUOTE_JSON_PATH = True
 452
 453    # Whether the text pattern/fill (3rd) parameter of RPAD()/LPAD() is optional (defaults to space)
 454    PAD_FILL_PATTERN_IS_REQUIRED = False
 455
 456    # Whether a projection can explode into multiple rows, e.g. by unnesting an array.
 457    SUPPORTS_EXPLODING_PROJECTIONS = True
 458
 459    # Whether ARRAY_CONCAT can be generated with varlen args or if it should be reduced to 2-arg version
 460    ARRAY_CONCAT_IS_VAR_LEN = True
 461
 462    # Whether CONVERT_TIMEZONE() is supported; if not, it will be generated as exp.AtTimeZone
 463    SUPPORTS_CONVERT_TIMEZONE = False
 464
 465    # Whether MEDIAN(expr) is supported; if not, it will be generated as PERCENTILE_CONT(expr, 0.5)
 466    SUPPORTS_MEDIAN = True
 467
 468    # Whether UNIX_SECONDS(timestamp) is supported
 469    SUPPORTS_UNIX_SECONDS = False
 470
 471    # Whether to wrap <props> in `AlterSet`, e.g., ALTER ... SET (<props>)
 472    ALTER_SET_WRAPPED = False
 473
 474    # Whether to normalize the date parts in EXTRACT(<date_part> FROM <expr>) into a common representation
 475    # For instance, to extract the day of week in ISO semantics, one can use ISODOW, DAYOFWEEKISO etc depending on the dialect.
 476    # TODO: The normalization should be done by default once we've tested it across all dialects.
 477    NORMALIZE_EXTRACT_DATE_PARTS = False
 478
 479    # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
 480    PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
 481
 482    # The function name of the exp.ArraySize expression
 483    ARRAY_SIZE_NAME: str = "ARRAY_LENGTH"
 484
 485    # The syntax to use when altering the type of a column
 486    ALTER_SET_TYPE = "SET DATA TYPE"
 487
 488    # Whether exp.ArraySize should generate the dimension arg too (valid for Postgres & DuckDB)
 489    # None -> Doesn't support it at all
 490    # False (DuckDB) -> Has backwards-compatible support, but preferably generated without
 491    # True (Postgres) -> Explicitly requires it
 492    ARRAY_SIZE_DIM_REQUIRED: t.Optional[bool] = None
 493
 494    # Whether a multi-argument DECODE(...) function is supported. If not, a CASE expression is generated
 495    SUPPORTS_DECODE_CASE = True
 496
 497    # Whether SYMMETRIC and ASYMMETRIC flags are supported with BETWEEN expression
 498    SUPPORTS_BETWEEN_FLAGS = False
 499
 500    # Whether LIKE and ILIKE support quantifiers such as LIKE ANY/ALL/SOME
 501    SUPPORTS_LIKE_QUANTIFIERS = True
 502
 503    TYPE_MAPPING = {
 504        exp.DataType.Type.DATETIME2: "TIMESTAMP",
 505        exp.DataType.Type.NCHAR: "CHAR",
 506        exp.DataType.Type.NVARCHAR: "VARCHAR",
 507        exp.DataType.Type.MEDIUMTEXT: "TEXT",
 508        exp.DataType.Type.LONGTEXT: "TEXT",
 509        exp.DataType.Type.TINYTEXT: "TEXT",
 510        exp.DataType.Type.BLOB: "VARBINARY",
 511        exp.DataType.Type.MEDIUMBLOB: "BLOB",
 512        exp.DataType.Type.LONGBLOB: "BLOB",
 513        exp.DataType.Type.TINYBLOB: "BLOB",
 514        exp.DataType.Type.INET: "INET",
 515        exp.DataType.Type.ROWVERSION: "VARBINARY",
 516        exp.DataType.Type.SMALLDATETIME: "TIMESTAMP",
 517    }
 518
 519    UNSUPPORTED_TYPES: set[exp.DataType.Type] = set()
 520
 521    TIME_PART_SINGULARS = {
 522        "MICROSECONDS": "MICROSECOND",
 523        "SECONDS": "SECOND",
 524        "MINUTES": "MINUTE",
 525        "HOURS": "HOUR",
 526        "DAYS": "DAY",
 527        "WEEKS": "WEEK",
 528        "MONTHS": "MONTH",
 529        "QUARTERS": "QUARTER",
 530        "YEARS": "YEAR",
 531    }
 532
 533    AFTER_HAVING_MODIFIER_TRANSFORMS = {
 534        "cluster": lambda self, e: self.sql(e, "cluster"),
 535        "distribute": lambda self, e: self.sql(e, "distribute"),
 536        "sort": lambda self, e: self.sql(e, "sort"),
 537        "windows": lambda self, e: (
 538            self.seg("WINDOW ") + self.expressions(e, key="windows", flat=True)
 539            if e.args.get("windows")
 540            else ""
 541        ),
 542        "qualify": lambda self, e: self.sql(e, "qualify"),
 543    }
 544
 545    TOKEN_MAPPING: t.Dict[TokenType, str] = {}
 546
 547    STRUCT_DELIMITER = ("<", ">")
 548
 549    PARAMETER_TOKEN = "@"
 550    NAMED_PLACEHOLDER_TOKEN = ":"
 551
 552    EXPRESSION_PRECEDES_PROPERTIES_CREATABLES: t.Set[str] = set()
 553
 554    PROPERTIES_LOCATION = {
 555        exp.AllowedValuesProperty: exp.Properties.Location.POST_SCHEMA,
 556        exp.AlgorithmProperty: exp.Properties.Location.POST_CREATE,
 557        exp.AutoIncrementProperty: exp.Properties.Location.POST_SCHEMA,
 558        exp.AutoRefreshProperty: exp.Properties.Location.POST_SCHEMA,
 559        exp.BackupProperty: exp.Properties.Location.POST_SCHEMA,
 560        exp.BlockCompressionProperty: exp.Properties.Location.POST_NAME,
 561        exp.CharacterSetProperty: exp.Properties.Location.POST_SCHEMA,
 562        exp.ChecksumProperty: exp.Properties.Location.POST_NAME,
 563        exp.CollateProperty: exp.Properties.Location.POST_SCHEMA,
 564        exp.CopyGrantsProperty: exp.Properties.Location.POST_SCHEMA,
 565        exp.Cluster: exp.Properties.Location.POST_SCHEMA,
 566        exp.ClusteredByProperty: exp.Properties.Location.POST_SCHEMA,
 567        exp.DistributedByProperty: exp.Properties.Location.POST_SCHEMA,
 568        exp.DuplicateKeyProperty: exp.Properties.Location.POST_SCHEMA,
 569        exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME,
 570        exp.DataDeletionProperty: exp.Properties.Location.POST_SCHEMA,
 571        exp.DefinerProperty: exp.Properties.Location.POST_CREATE,
 572        exp.DictRange: exp.Properties.Location.POST_SCHEMA,
 573        exp.DictProperty: exp.Properties.Location.POST_SCHEMA,
 574        exp.DynamicProperty: exp.Properties.Location.POST_CREATE,
 575        exp.DistKeyProperty: exp.Properties.Location.POST_SCHEMA,
 576        exp.DistStyleProperty: exp.Properties.Location.POST_SCHEMA,
 577        exp.EmptyProperty: exp.Properties.Location.POST_SCHEMA,
 578        exp.EncodeProperty: exp.Properties.Location.POST_EXPRESSION,
 579        exp.EngineProperty: exp.Properties.Location.POST_SCHEMA,
 580        exp.EnviromentProperty: exp.Properties.Location.POST_SCHEMA,
 581        exp.ExecuteAsProperty: exp.Properties.Location.POST_SCHEMA,
 582        exp.ExternalProperty: exp.Properties.Location.POST_CREATE,
 583        exp.FallbackProperty: exp.Properties.Location.POST_NAME,
 584        exp.FileFormatProperty: exp.Properties.Location.POST_WITH,
 585        exp.FreespaceProperty: exp.Properties.Location.POST_NAME,
 586        exp.GlobalProperty: exp.Properties.Location.POST_CREATE,
 587        exp.HeapProperty: exp.Properties.Location.POST_WITH,
 588        exp.InheritsProperty: exp.Properties.Location.POST_SCHEMA,
 589        exp.IcebergProperty: exp.Properties.Location.POST_CREATE,
 590        exp.IncludeProperty: exp.Properties.Location.POST_SCHEMA,
 591        exp.InputModelProperty: exp.Properties.Location.POST_SCHEMA,
 592        exp.IsolatedLoadingProperty: exp.Properties.Location.POST_NAME,
 593        exp.JournalProperty: exp.Properties.Location.POST_NAME,
 594        exp.LanguageProperty: exp.Properties.Location.POST_SCHEMA,
 595        exp.LikeProperty: exp.Properties.Location.POST_SCHEMA,
 596        exp.LocationProperty: exp.Properties.Location.POST_SCHEMA,
 597        exp.LockProperty: exp.Properties.Location.POST_SCHEMA,
 598        exp.LockingProperty: exp.Properties.Location.POST_ALIAS,
 599        exp.LogProperty: exp.Properties.Location.POST_NAME,
 600        exp.MaterializedProperty: exp.Properties.Location.POST_CREATE,
 601        exp.MergeBlockRatioProperty: exp.Properties.Location.POST_NAME,
 602        exp.NoPrimaryIndexProperty: exp.Properties.Location.POST_EXPRESSION,
 603        exp.OnProperty: exp.Properties.Location.POST_SCHEMA,
 604        exp.OnCommitProperty: exp.Properties.Location.POST_EXPRESSION,
 605        exp.Order: exp.Properties.Location.POST_SCHEMA,
 606        exp.OutputModelProperty: exp.Properties.Location.POST_SCHEMA,
 607        exp.PartitionedByProperty: exp.Properties.Location.POST_WITH,
 608        exp.PartitionedOfProperty: exp.Properties.Location.POST_SCHEMA,
 609        exp.PrimaryKey: exp.Properties.Location.POST_SCHEMA,
 610        exp.Property: exp.Properties.Location.POST_WITH,
 611        exp.RemoteWithConnectionModelProperty: exp.Properties.Location.POST_SCHEMA,
 612        exp.ReturnsProperty: exp.Properties.Location.POST_SCHEMA,
 613        exp.RowFormatProperty: exp.Properties.Location.POST_SCHEMA,
 614        exp.RowFormatDelimitedProperty: exp.Properties.Location.POST_SCHEMA,
 615        exp.RowFormatSerdeProperty: exp.Properties.Location.POST_SCHEMA,
 616        exp.SampleProperty: exp.Properties.Location.POST_SCHEMA,
 617        exp.SchemaCommentProperty: exp.Properties.Location.POST_SCHEMA,
 618        exp.SecureProperty: exp.Properties.Location.POST_CREATE,
 619        exp.SecurityProperty: exp.Properties.Location.POST_SCHEMA,
 620        exp.SerdeProperties: exp.Properties.Location.POST_SCHEMA,
 621        exp.Set: exp.Properties.Location.POST_SCHEMA,
 622        exp.SettingsProperty: exp.Properties.Location.POST_SCHEMA,
 623        exp.SetProperty: exp.Properties.Location.POST_CREATE,
 624        exp.SetConfigProperty: exp.Properties.Location.POST_SCHEMA,
 625        exp.SharingProperty: exp.Properties.Location.POST_EXPRESSION,
 626        exp.SequenceProperties: exp.Properties.Location.POST_EXPRESSION,
 627        exp.SortKeyProperty: exp.Properties.Location.POST_SCHEMA,
 628        exp.SqlReadWriteProperty: exp.Properties.Location.POST_SCHEMA,
 629        exp.SqlSecurityProperty: exp.Properties.Location.POST_CREATE,
 630        exp.StabilityProperty: exp.Properties.Location.POST_SCHEMA,
 631        exp.StorageHandlerProperty: exp.Properties.Location.POST_SCHEMA,
 632        exp.StreamingTableProperty: exp.Properties.Location.POST_CREATE,
 633        exp.StrictProperty: exp.Properties.Location.POST_SCHEMA,
 634        exp.Tags: exp.Properties.Location.POST_WITH,
 635        exp.TemporaryProperty: exp.Properties.Location.POST_CREATE,
 636        exp.ToTableProperty: exp.Properties.Location.POST_SCHEMA,
 637        exp.TransientProperty: exp.Properties.Location.POST_CREATE,
 638        exp.TransformModelProperty: exp.Properties.Location.POST_SCHEMA,
 639        exp.MergeTreeTTL: exp.Properties.Location.POST_SCHEMA,
 640        exp.UnloggedProperty: exp.Properties.Location.POST_CREATE,
 641        exp.UsingTemplateProperty: exp.Properties.Location.POST_SCHEMA,
 642        exp.ViewAttributeProperty: exp.Properties.Location.POST_SCHEMA,
 643        exp.VolatileProperty: exp.Properties.Location.POST_CREATE,
 644        exp.WithDataProperty: exp.Properties.Location.POST_EXPRESSION,
 645        exp.WithJournalTableProperty: exp.Properties.Location.POST_NAME,
 646        exp.WithProcedureOptions: exp.Properties.Location.POST_SCHEMA,
 647        exp.WithSchemaBindingProperty: exp.Properties.Location.POST_SCHEMA,
 648        exp.WithSystemVersioningProperty: exp.Properties.Location.POST_SCHEMA,
 649        exp.ForceProperty: exp.Properties.Location.POST_CREATE,
 650    }
 651
 652    # Keywords that can't be used as unquoted identifier names
 653    RESERVED_KEYWORDS: t.Set[str] = set()
 654
 655    # Expressions whose comments are separated from them for better formatting
 656    WITH_SEPARATED_COMMENTS: t.Tuple[t.Type[exp.Expression], ...] = (
 657        exp.Command,
 658        exp.Create,
 659        exp.Describe,
 660        exp.Delete,
 661        exp.Drop,
 662        exp.From,
 663        exp.Insert,
 664        exp.Join,
 665        exp.MultitableInserts,
 666        exp.Order,
 667        exp.Group,
 668        exp.Having,
 669        exp.Select,
 670        exp.SetOperation,
 671        exp.Update,
 672        exp.Where,
 673        exp.With,
 674    )
 675
 676    # Expressions that should not have their comments generated in maybe_comment
 677    EXCLUDE_COMMENTS: t.Tuple[t.Type[exp.Expression], ...] = (
 678        exp.Binary,
 679        exp.SetOperation,
 680    )
 681
 682    # Expressions that can remain unwrapped when appearing in the context of an INTERVAL
 683    UNWRAPPED_INTERVAL_VALUES: t.Tuple[t.Type[exp.Expression], ...] = (
 684        exp.Column,
 685        exp.Literal,
 686        exp.Neg,
 687        exp.Paren,
 688    )
 689
 690    PARAMETERIZABLE_TEXT_TYPES = {
 691        exp.DataType.Type.NVARCHAR,
 692        exp.DataType.Type.VARCHAR,
 693        exp.DataType.Type.CHAR,
 694        exp.DataType.Type.NCHAR,
 695    }
 696
 697    # Expressions that need to have all CTEs under them bubbled up to them
 698    EXPRESSIONS_WITHOUT_NESTED_CTES: t.Set[t.Type[exp.Expression]] = set()
 699
 700    RESPECT_IGNORE_NULLS_UNSUPPORTED_EXPRESSIONS: t.Tuple[t.Type[exp.Expression], ...] = ()
 701
 702    SAFE_JSON_PATH_KEY_RE = exp.SAFE_IDENTIFIER_RE
 703
 704    SENTINEL_LINE_BREAK = "__SQLGLOT__LB__"
 705
 706    __slots__ = (
 707        "pretty",
 708        "identify",
 709        "normalize",
 710        "pad",
 711        "_indent",
 712        "normalize_functions",
 713        "unsupported_level",
 714        "max_unsupported",
 715        "leading_comma",
 716        "max_text_width",
 717        "comments",
 718        "dialect",
 719        "unsupported_messages",
 720        "_escaped_quote_end",
 721        "_escaped_identifier_end",
 722        "_next_name",
 723        "_identifier_start",
 724        "_identifier_end",
 725        "_quote_json_path_key_using_brackets",
 726    )
 727
 728    def __init__(
 729        self,
 730        pretty: t.Optional[bool] = None,
 731        identify: str | bool = False,
 732        normalize: bool = False,
 733        pad: int = 2,
 734        indent: int = 2,
 735        normalize_functions: t.Optional[str | bool] = None,
 736        unsupported_level: ErrorLevel = ErrorLevel.WARN,
 737        max_unsupported: int = 3,
 738        leading_comma: bool = False,
 739        max_text_width: int = 80,
 740        comments: bool = True,
 741        dialect: DialectType = None,
 742    ):
 743        import sqlglot
 744        from sqlglot.dialects import Dialect
 745
 746        self.pretty = pretty if pretty is not None else sqlglot.pretty
 747        self.identify = identify
 748        self.normalize = normalize
 749        self.pad = pad
 750        self._indent = indent
 751        self.unsupported_level = unsupported_level
 752        self.max_unsupported = max_unsupported
 753        self.leading_comma = leading_comma
 754        self.max_text_width = max_text_width
 755        self.comments = comments
 756        self.dialect = Dialect.get_or_raise(dialect)
 757
 758        # This is both a Dialect property and a Generator argument, so we prioritize the latter
 759        self.normalize_functions = (
 760            self.dialect.NORMALIZE_FUNCTIONS if normalize_functions is None else normalize_functions
 761        )
 762
 763        self.unsupported_messages: t.List[str] = []
 764        self._escaped_quote_end: str = (
 765            self.dialect.tokenizer_class.STRING_ESCAPES[0] + self.dialect.QUOTE_END
 766        )
 767        self._escaped_identifier_end = self.dialect.IDENTIFIER_END * 2
 768
 769        self._next_name = name_sequence("_t")
 770
 771        self._identifier_start = self.dialect.IDENTIFIER_START
 772        self._identifier_end = self.dialect.IDENTIFIER_END
 773
 774        self._quote_json_path_key_using_brackets = True
 775
 776    def generate(self, expression: exp.Expression, copy: bool = True) -> str:
 777        """
 778        Generates the SQL string corresponding to the given syntax tree.
 779
 780        Args:
 781            expression: The syntax tree.
 782            copy: Whether to copy the expression. The generator performs mutations so
 783                it is safer to copy.
 784
 785        Returns:
 786            The SQL string corresponding to `expression`.
 787        """
 788        if copy:
 789            expression = expression.copy()
 790
 791        expression = self.preprocess(expression)
 792
 793        self.unsupported_messages = []
 794        sql = self.sql(expression).strip()
 795
 796        if self.pretty:
 797            sql = sql.replace(self.SENTINEL_LINE_BREAK, "\n")
 798
 799        if self.unsupported_level == ErrorLevel.IGNORE:
 800            return sql
 801
 802        if self.unsupported_level == ErrorLevel.WARN:
 803            for msg in self.unsupported_messages:
 804                logger.warning(msg)
 805        elif self.unsupported_level == ErrorLevel.RAISE and self.unsupported_messages:
 806            raise UnsupportedError(concat_messages(self.unsupported_messages, self.max_unsupported))
 807
 808        return sql
 809
 810    def preprocess(self, expression: exp.Expression) -> exp.Expression:
 811        """Apply generic preprocessing transformations to a given expression."""
 812        expression = self._move_ctes_to_top_level(expression)
 813
 814        if self.ENSURE_BOOLS:
 815            from sqlglot.transforms import ensure_bools
 816
 817            expression = ensure_bools(expression)
 818
 819        return expression
 820
 821    def _move_ctes_to_top_level(self, expression: E) -> E:
 822        if (
 823            not expression.parent
 824            and type(expression) in self.EXPRESSIONS_WITHOUT_NESTED_CTES
 825            and any(node.parent is not expression for node in expression.find_all(exp.With))
 826        ):
 827            from sqlglot.transforms import move_ctes_to_top_level
 828
 829            expression = move_ctes_to_top_level(expression)
 830        return expression
 831
 832    def unsupported(self, message: str) -> None:
 833        if self.unsupported_level == ErrorLevel.IMMEDIATE:
 834            raise UnsupportedError(message)
 835        self.unsupported_messages.append(message)
 836
 837    def sep(self, sep: str = " ") -> str:
 838        return f"{sep.strip()}\n" if self.pretty else sep
 839
 840    def seg(self, sql: str, sep: str = " ") -> str:
 841        return f"{self.sep(sep)}{sql}"
 842
 843    def sanitize_comment(self, comment: str) -> str:
 844        comment = " " + comment if comment[0].strip() else comment
 845        comment = comment + " " if comment[-1].strip() else comment
 846
 847        if not self.dialect.tokenizer_class.NESTED_COMMENTS:
 848            # Necessary workaround to avoid syntax errors due to nesting: /* ... */ ... */
 849            comment = comment.replace("*/", "* /")
 850
 851        return comment
 852
 853    def maybe_comment(
 854        self,
 855        sql: str,
 856        expression: t.Optional[exp.Expression] = None,
 857        comments: t.Optional[t.List[str]] = None,
 858        separated: bool = False,
 859    ) -> str:
 860        comments = (
 861            ((expression and expression.comments) if comments is None else comments)  # type: ignore
 862            if self.comments
 863            else None
 864        )
 865
 866        if not comments or isinstance(expression, self.EXCLUDE_COMMENTS):
 867            return sql
 868
 869        comments_sql = " ".join(
 870            f"/*{self.sanitize_comment(comment)}*/" for comment in comments if comment
 871        )
 872
 873        if not comments_sql:
 874            return sql
 875
 876        comments_sql = self._replace_line_breaks(comments_sql)
 877
 878        if separated or isinstance(expression, self.WITH_SEPARATED_COMMENTS):
 879            return (
 880                f"{self.sep()}{comments_sql}{sql}"
 881                if not sql or sql[0].isspace()
 882                else f"{comments_sql}{self.sep()}{sql}"
 883            )
 884
 885        return f"{sql} {comments_sql}"
 886
 887    def wrap(self, expression: exp.Expression | str) -> str:
 888        this_sql = (
 889            self.sql(expression)
 890            if isinstance(expression, exp.UNWRAPPED_QUERIES)
 891            else self.sql(expression, "this")
 892        )
 893        if not this_sql:
 894            return "()"
 895
 896        this_sql = self.indent(this_sql, level=1, pad=0)
 897        return f"({self.sep('')}{this_sql}{self.seg(')', sep='')}"
 898
 899    def no_identify(self, func: t.Callable[..., str], *args, **kwargs) -> str:
 900        original = self.identify
 901        self.identify = False
 902        result = func(*args, **kwargs)
 903        self.identify = original
 904        return result
 905
 906    def normalize_func(self, name: str) -> str:
 907        if self.normalize_functions == "upper" or self.normalize_functions is True:
 908            return name.upper()
 909        if self.normalize_functions == "lower":
 910            return name.lower()
 911        return name
 912
 913    def indent(
 914        self,
 915        sql: str,
 916        level: int = 0,
 917        pad: t.Optional[int] = None,
 918        skip_first: bool = False,
 919        skip_last: bool = False,
 920    ) -> str:
 921        if not self.pretty or not sql:
 922            return sql
 923
 924        pad = self.pad if pad is None else pad
 925        lines = sql.split("\n")
 926
 927        return "\n".join(
 928            (
 929                line
 930                if (skip_first and i == 0) or (skip_last and i == len(lines) - 1)
 931                else f"{' ' * (level * self._indent + pad)}{line}"
 932            )
 933            for i, line in enumerate(lines)
 934        )
 935
 936    def sql(
 937        self,
 938        expression: t.Optional[str | exp.Expression],
 939        key: t.Optional[str] = None,
 940        comment: bool = True,
 941    ) -> str:
 942        if not expression:
 943            return ""
 944
 945        if isinstance(expression, str):
 946            return expression
 947
 948        if key:
 949            value = expression.args.get(key)
 950            if value:
 951                return self.sql(value)
 952            return ""
 953
 954        transform = self.TRANSFORMS.get(expression.__class__)
 955
 956        if callable(transform):
 957            sql = transform(self, expression)
 958        elif isinstance(expression, exp.Expression):
 959            exp_handler_name = f"{expression.key}_sql"
 960
 961            if hasattr(self, exp_handler_name):
 962                sql = getattr(self, exp_handler_name)(expression)
 963            elif isinstance(expression, exp.Func):
 964                sql = self.function_fallback_sql(expression)
 965            elif isinstance(expression, exp.Property):
 966                sql = self.property_sql(expression)
 967            else:
 968                raise ValueError(f"Unsupported expression type {expression.__class__.__name__}")
 969        else:
 970            raise ValueError(f"Expected an Expression. Received {type(expression)}: {expression}")
 971
 972        return self.maybe_comment(sql, expression) if self.comments and comment else sql
 973
 974    def uncache_sql(self, expression: exp.Uncache) -> str:
 975        table = self.sql(expression, "this")
 976        exists_sql = " IF EXISTS" if expression.args.get("exists") else ""
 977        return f"UNCACHE TABLE{exists_sql} {table}"
 978
 979    def cache_sql(self, expression: exp.Cache) -> str:
 980        lazy = " LAZY" if expression.args.get("lazy") else ""
 981        table = self.sql(expression, "this")
 982        options = expression.args.get("options")
 983        options = f" OPTIONS({self.sql(options[0])} = {self.sql(options[1])})" if options else ""
 984        sql = self.sql(expression, "expression")
 985        sql = f" AS{self.sep()}{sql}" if sql else ""
 986        sql = f"CACHE{lazy} TABLE {table}{options}{sql}"
 987        return self.prepend_ctes(expression, sql)
 988
 989    def characterset_sql(self, expression: exp.CharacterSet) -> str:
 990        if isinstance(expression.parent, exp.Cast):
 991            return f"CHAR CHARACTER SET {self.sql(expression, 'this')}"
 992        default = "DEFAULT " if expression.args.get("default") else ""
 993        return f"{default}CHARACTER SET={self.sql(expression, 'this')}"
 994
 995    def column_parts(self, expression: exp.Column) -> str:
 996        return ".".join(
 997            self.sql(part)
 998            for part in (
 999                expression.args.get("catalog"),
1000                expression.args.get("db"),
1001                expression.args.get("table"),
1002                expression.args.get("this"),
1003            )
1004            if part
1005        )
1006
1007    def column_sql(self, expression: exp.Column) -> str:
1008        join_mark = " (+)" if expression.args.get("join_mark") else ""
1009
1010        if join_mark and not self.dialect.SUPPORTS_COLUMN_JOIN_MARKS:
1011            join_mark = ""
1012            self.unsupported("Outer join syntax using the (+) operator is not supported.")
1013
1014        return f"{self.column_parts(expression)}{join_mark}"
1015
1016    def columnposition_sql(self, expression: exp.ColumnPosition) -> str:
1017        this = self.sql(expression, "this")
1018        this = f" {this}" if this else ""
1019        position = self.sql(expression, "position")
1020        return f"{position}{this}"
1021
1022    def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
1023        column = self.sql(expression, "this")
1024        kind = self.sql(expression, "kind")
1025        constraints = self.expressions(expression, key="constraints", sep=" ", flat=True)
1026        exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
1027        kind = f"{sep}{kind}" if kind else ""
1028        constraints = f" {constraints}" if constraints else ""
1029        position = self.sql(expression, "position")
1030        position = f" {position}" if position else ""
1031
1032        if expression.find(exp.ComputedColumnConstraint) and not self.COMPUTED_COLUMN_WITH_TYPE:
1033            kind = ""
1034
1035        return f"{exists}{column}{kind}{constraints}{position}"
1036
1037    def columnconstraint_sql(self, expression: exp.ColumnConstraint) -> str:
1038        this = self.sql(expression, "this")
1039        kind_sql = self.sql(expression, "kind").strip()
1040        return f"CONSTRAINT {this} {kind_sql}" if this else kind_sql
1041
1042    def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
1043        this = self.sql(expression, "this")
1044        if expression.args.get("not_null"):
1045            persisted = " PERSISTED NOT NULL"
1046        elif expression.args.get("persisted"):
1047            persisted = " PERSISTED"
1048        else:
1049            persisted = ""
1050
1051        return f"AS {this}{persisted}"
1052
1053    def autoincrementcolumnconstraint_sql(self, _) -> str:
1054        return self.token_sql(TokenType.AUTO_INCREMENT)
1055
1056    def compresscolumnconstraint_sql(self, expression: exp.CompressColumnConstraint) -> str:
1057        if isinstance(expression.this, list):
1058            this = self.wrap(self.expressions(expression, key="this", flat=True))
1059        else:
1060            this = self.sql(expression, "this")
1061
1062        return f"COMPRESS {this}"
1063
1064    def generatedasidentitycolumnconstraint_sql(
1065        self, expression: exp.GeneratedAsIdentityColumnConstraint
1066    ) -> str:
1067        this = ""
1068        if expression.this is not None:
1069            on_null = " ON NULL" if expression.args.get("on_null") else ""
1070            this = " ALWAYS" if expression.this else f" BY DEFAULT{on_null}"
1071
1072        start = expression.args.get("start")
1073        start = f"START WITH {start}" if start else ""
1074        increment = expression.args.get("increment")
1075        increment = f" INCREMENT BY {increment}" if increment else ""
1076        minvalue = expression.args.get("minvalue")
1077        minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1078        maxvalue = expression.args.get("maxvalue")
1079        maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1080        cycle = expression.args.get("cycle")
1081        cycle_sql = ""
1082
1083        if cycle is not None:
1084            cycle_sql = f"{' NO' if not cycle else ''} CYCLE"
1085            cycle_sql = cycle_sql.strip() if not start and not increment else cycle_sql
1086
1087        sequence_opts = ""
1088        if start or increment or cycle_sql:
1089            sequence_opts = f"{start}{increment}{minvalue}{maxvalue}{cycle_sql}"
1090            sequence_opts = f" ({sequence_opts.strip()})"
1091
1092        expr = self.sql(expression, "expression")
1093        expr = f"({expr})" if expr else "IDENTITY"
1094
1095        return f"GENERATED{this} AS {expr}{sequence_opts}"
1096
1097    def generatedasrowcolumnconstraint_sql(
1098        self, expression: exp.GeneratedAsRowColumnConstraint
1099    ) -> str:
1100        start = "START" if expression.args.get("start") else "END"
1101        hidden = " HIDDEN" if expression.args.get("hidden") else ""
1102        return f"GENERATED ALWAYS AS ROW {start}{hidden}"
1103
1104    def periodforsystemtimeconstraint_sql(
1105        self, expression: exp.PeriodForSystemTimeConstraint
1106    ) -> str:
1107        return f"PERIOD FOR SYSTEM_TIME ({self.sql(expression, 'this')}, {self.sql(expression, 'expression')})"
1108
1109    def notnullcolumnconstraint_sql(self, expression: exp.NotNullColumnConstraint) -> str:
1110        return f"{'' if expression.args.get('allow_null') else 'NOT '}NULL"
1111
1112    def primarykeycolumnconstraint_sql(self, expression: exp.PrimaryKeyColumnConstraint) -> str:
1113        desc = expression.args.get("desc")
1114        if desc is not None:
1115            return f"PRIMARY KEY{' DESC' if desc else ' ASC'}"
1116        options = self.expressions(expression, key="options", flat=True, sep=" ")
1117        options = f" {options}" if options else ""
1118        return f"PRIMARY KEY{options}"
1119
1120    def uniquecolumnconstraint_sql(self, expression: exp.UniqueColumnConstraint) -> str:
1121        this = self.sql(expression, "this")
1122        this = f" {this}" if this else ""
1123        index_type = expression.args.get("index_type")
1124        index_type = f" USING {index_type}" if index_type else ""
1125        on_conflict = self.sql(expression, "on_conflict")
1126        on_conflict = f" {on_conflict}" if on_conflict else ""
1127        nulls_sql = " NULLS NOT DISTINCT" if expression.args.get("nulls") else ""
1128        options = self.expressions(expression, key="options", flat=True, sep=" ")
1129        options = f" {options}" if options else ""
1130        return f"UNIQUE{nulls_sql}{this}{index_type}{on_conflict}{options}"
1131
1132    def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str:
1133        return self.sql(expression, "this")
1134
1135    def create_sql(self, expression: exp.Create) -> str:
1136        kind = self.sql(expression, "kind")
1137        kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1138        properties = expression.args.get("properties")
1139        properties_locs = self.locate_properties(properties) if properties else defaultdict()
1140
1141        this = self.createable_sql(expression, properties_locs)
1142
1143        properties_sql = ""
1144        if properties_locs.get(exp.Properties.Location.POST_SCHEMA) or properties_locs.get(
1145            exp.Properties.Location.POST_WITH
1146        ):
1147            props_ast = exp.Properties(
1148                expressions=[
1149                    *properties_locs[exp.Properties.Location.POST_SCHEMA],
1150                    *properties_locs[exp.Properties.Location.POST_WITH],
1151                ]
1152            )
1153            props_ast.parent = expression
1154            properties_sql = self.sql(props_ast)
1155
1156            if properties_locs.get(exp.Properties.Location.POST_SCHEMA):
1157                properties_sql = self.sep() + properties_sql
1158            elif not self.pretty:
1159                # Standalone POST_WITH properties need a leading whitespace in non-pretty mode
1160                properties_sql = f" {properties_sql}"
1161
1162        begin = " BEGIN" if expression.args.get("begin") else ""
1163        end = " END" if expression.args.get("end") else ""
1164
1165        expression_sql = self.sql(expression, "expression")
1166        if expression_sql:
1167            expression_sql = f"{begin}{self.sep()}{expression_sql}{end}"
1168
1169            if self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return):
1170                postalias_props_sql = ""
1171                if properties_locs.get(exp.Properties.Location.POST_ALIAS):
1172                    postalias_props_sql = self.properties(
1173                        exp.Properties(
1174                            expressions=properties_locs[exp.Properties.Location.POST_ALIAS]
1175                        ),
1176                        wrapped=False,
1177                    )
1178                postalias_props_sql = f" {postalias_props_sql}" if postalias_props_sql else ""
1179                expression_sql = f" AS{postalias_props_sql}{expression_sql}"
1180
1181        postindex_props_sql = ""
1182        if properties_locs.get(exp.Properties.Location.POST_INDEX):
1183            postindex_props_sql = self.properties(
1184                exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_INDEX]),
1185                wrapped=False,
1186                prefix=" ",
1187            )
1188
1189        indexes = self.expressions(expression, key="indexes", indent=False, sep=" ")
1190        indexes = f" {indexes}" if indexes else ""
1191        index_sql = indexes + postindex_props_sql
1192
1193        replace = " OR REPLACE" if expression.args.get("replace") else ""
1194        refresh = " OR REFRESH" if expression.args.get("refresh") else ""
1195        unique = " UNIQUE" if expression.args.get("unique") else ""
1196
1197        clustered = expression.args.get("clustered")
1198        if clustered is None:
1199            clustered_sql = ""
1200        elif clustered:
1201            clustered_sql = " CLUSTERED COLUMNSTORE"
1202        else:
1203            clustered_sql = " NONCLUSTERED COLUMNSTORE"
1204
1205        postcreate_props_sql = ""
1206        if properties_locs.get(exp.Properties.Location.POST_CREATE):
1207            postcreate_props_sql = self.properties(
1208                exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_CREATE]),
1209                sep=" ",
1210                prefix=" ",
1211                wrapped=False,
1212            )
1213
1214        modifiers = "".join((clustered_sql, replace, refresh, unique, postcreate_props_sql))
1215
1216        postexpression_props_sql = ""
1217        if properties_locs.get(exp.Properties.Location.POST_EXPRESSION):
1218            postexpression_props_sql = self.properties(
1219                exp.Properties(
1220                    expressions=properties_locs[exp.Properties.Location.POST_EXPRESSION]
1221                ),
1222                sep=" ",
1223                prefix=" ",
1224                wrapped=False,
1225            )
1226
1227        concurrently = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1228        exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else ""
1229        no_schema_binding = (
1230            " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else ""
1231        )
1232
1233        clone = self.sql(expression, "clone")
1234        clone = f" {clone}" if clone else ""
1235
1236        if kind in self.EXPRESSION_PRECEDES_PROPERTIES_CREATABLES:
1237            properties_expression = f"{expression_sql}{properties_sql}"
1238        else:
1239            properties_expression = f"{properties_sql}{expression_sql}"
1240
1241        expression_sql = f"CREATE{modifiers} {kind}{concurrently}{exists_sql} {this}{properties_expression}{postexpression_props_sql}{index_sql}{no_schema_binding}{clone}"
1242        return self.prepend_ctes(expression, expression_sql)
1243
1244    def sequenceproperties_sql(self, expression: exp.SequenceProperties) -> str:
1245        start = self.sql(expression, "start")
1246        start = f"START WITH {start}" if start else ""
1247        increment = self.sql(expression, "increment")
1248        increment = f" INCREMENT BY {increment}" if increment else ""
1249        minvalue = self.sql(expression, "minvalue")
1250        minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1251        maxvalue = self.sql(expression, "maxvalue")
1252        maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1253        owned = self.sql(expression, "owned")
1254        owned = f" OWNED BY {owned}" if owned else ""
1255
1256        cache = expression.args.get("cache")
1257        if cache is None:
1258            cache_str = ""
1259        elif cache is True:
1260            cache_str = " CACHE"
1261        else:
1262            cache_str = f" CACHE {cache}"
1263
1264        options = self.expressions(expression, key="options", flat=True, sep=" ")
1265        options = f" {options}" if options else ""
1266
1267        return f"{start}{increment}{minvalue}{maxvalue}{cache_str}{options}{owned}".lstrip()
1268
1269    def clone_sql(self, expression: exp.Clone) -> str:
1270        this = self.sql(expression, "this")
1271        shallow = "SHALLOW " if expression.args.get("shallow") else ""
1272        keyword = "COPY" if expression.args.get("copy") and self.SUPPORTS_TABLE_COPY else "CLONE"
1273        return f"{shallow}{keyword} {this}"
1274
1275    def describe_sql(self, expression: exp.Describe) -> str:
1276        style = expression.args.get("style")
1277        style = f" {style}" if style else ""
1278        partition = self.sql(expression, "partition")
1279        partition = f" {partition}" if partition else ""
1280        format = self.sql(expression, "format")
1281        format = f" {format}" if format else ""
1282
1283        return f"DESCRIBE{style}{format} {self.sql(expression, 'this')}{partition}"
1284
1285    def heredoc_sql(self, expression: exp.Heredoc) -> str:
1286        tag = self.sql(expression, "tag")
1287        return f"${tag}${self.sql(expression, 'this')}${tag}$"
1288
1289    def prepend_ctes(self, expression: exp.Expression, sql: str) -> str:
1290        with_ = self.sql(expression, "with")
1291        if with_:
1292            sql = f"{with_}{self.sep()}{sql}"
1293        return sql
1294
1295    def with_sql(self, expression: exp.With) -> str:
1296        sql = self.expressions(expression, flat=True)
1297        recursive = (
1298            "RECURSIVE "
1299            if self.CTE_RECURSIVE_KEYWORD_REQUIRED and expression.args.get("recursive")
1300            else ""
1301        )
1302        search = self.sql(expression, "search")
1303        search = f" {search}" if search else ""
1304
1305        return f"WITH {recursive}{sql}{search}"
1306
1307    def cte_sql(self, expression: exp.CTE) -> str:
1308        alias = expression.args.get("alias")
1309        if alias:
1310            alias.add_comments(expression.pop_comments())
1311
1312        alias_sql = self.sql(expression, "alias")
1313
1314        materialized = expression.args.get("materialized")
1315        if materialized is False:
1316            materialized = "NOT MATERIALIZED "
1317        elif materialized:
1318            materialized = "MATERIALIZED "
1319
1320        return f"{alias_sql} AS {materialized or ''}{self.wrap(expression)}"
1321
1322    def tablealias_sql(self, expression: exp.TableAlias) -> str:
1323        alias = self.sql(expression, "this")
1324        columns = self.expressions(expression, key="columns", flat=True)
1325        columns = f"({columns})" if columns else ""
1326
1327        if columns and not self.SUPPORTS_TABLE_ALIAS_COLUMNS:
1328            columns = ""
1329            self.unsupported("Named columns are not supported in table alias.")
1330
1331        if not alias and not self.dialect.UNNEST_COLUMN_ONLY:
1332            alias = self._next_name()
1333
1334        return f"{alias}{columns}"
1335
1336    def bitstring_sql(self, expression: exp.BitString) -> str:
1337        this = self.sql(expression, "this")
1338        if self.dialect.BIT_START:
1339            return f"{self.dialect.BIT_START}{this}{self.dialect.BIT_END}"
1340        return f"{int(this, 2)}"
1341
1342    def hexstring_sql(
1343        self, expression: exp.HexString, binary_function_repr: t.Optional[str] = None
1344    ) -> str:
1345        this = self.sql(expression, "this")
1346        is_integer_type = expression.args.get("is_integer")
1347
1348        if (is_integer_type and not self.dialect.HEX_STRING_IS_INTEGER_TYPE) or (
1349            not self.dialect.HEX_START and not binary_function_repr
1350        ):
1351            # Integer representation will be returned if:
1352            # - The read dialect treats the hex value as integer literal but not the write
1353            # - The transpilation is not supported (write dialect hasn't set HEX_START or the param flag)
1354            return f"{int(this, 16)}"
1355
1356        if not is_integer_type:
1357            # Read dialect treats the hex value as BINARY/BLOB
1358            if binary_function_repr:
1359                # The write dialect supports the transpilation to its equivalent BINARY/BLOB
1360                return self.func(binary_function_repr, exp.Literal.string(this))
1361            if self.dialect.HEX_STRING_IS_INTEGER_TYPE:
1362                # The write dialect does not support the transpilation, it'll treat the hex value as INTEGER
1363                self.unsupported("Unsupported transpilation from BINARY/BLOB hex string")
1364
1365        return f"{self.dialect.HEX_START}{this}{self.dialect.HEX_END}"
1366
1367    def bytestring_sql(self, expression: exp.ByteString) -> str:
1368        this = self.sql(expression, "this")
1369        if self.dialect.BYTE_START:
1370            return f"{self.dialect.BYTE_START}{this}{self.dialect.BYTE_END}"
1371        return this
1372
1373    def unicodestring_sql(self, expression: exp.UnicodeString) -> str:
1374        this = self.sql(expression, "this")
1375        escape = expression.args.get("escape")
1376
1377        if self.dialect.UNICODE_START:
1378            escape_substitute = r"\\\1"
1379            left_quote, right_quote = self.dialect.UNICODE_START, self.dialect.UNICODE_END
1380        else:
1381            escape_substitute = r"\\u\1"
1382            left_quote, right_quote = self.dialect.QUOTE_START, self.dialect.QUOTE_END
1383
1384        if escape:
1385            escape_pattern = re.compile(rf"{escape.name}(\d+)")
1386            escape_sql = f" UESCAPE {self.sql(escape)}" if self.SUPPORTS_UESCAPE else ""
1387        else:
1388            escape_pattern = ESCAPED_UNICODE_RE
1389            escape_sql = ""
1390
1391        if not self.dialect.UNICODE_START or (escape and not self.SUPPORTS_UESCAPE):
1392            this = escape_pattern.sub(self.UNICODE_SUBSTITUTE or escape_substitute, this)
1393
1394        return f"{left_quote}{this}{right_quote}{escape_sql}"
1395
1396    def rawstring_sql(self, expression: exp.RawString) -> str:
1397        string = expression.this
1398        if "\\" in self.dialect.tokenizer_class.STRING_ESCAPES:
1399            string = string.replace("\\", "\\\\")
1400
1401        string = self.escape_str(string, escape_backslash=False)
1402        return f"{self.dialect.QUOTE_START}{string}{self.dialect.QUOTE_END}"
1403
1404    def datatypeparam_sql(self, expression: exp.DataTypeParam) -> str:
1405        this = self.sql(expression, "this")
1406        specifier = self.sql(expression, "expression")
1407        specifier = f" {specifier}" if specifier and self.DATA_TYPE_SPECIFIERS_ALLOWED else ""
1408        return f"{this}{specifier}"
1409
1410    def datatype_sql(self, expression: exp.DataType) -> str:
1411        nested = ""
1412        values = ""
1413        interior = self.expressions(expression, flat=True)
1414
1415        type_value = expression.this
1416        if type_value in self.UNSUPPORTED_TYPES:
1417            self.unsupported(
1418                f"Data type {type_value.value} is not supported when targeting {self.dialect.__class__.__name__}"
1419            )
1420
1421        if type_value == exp.DataType.Type.USERDEFINED and expression.args.get("kind"):
1422            type_sql = self.sql(expression, "kind")
1423        else:
1424            type_sql = (
1425                self.TYPE_MAPPING.get(type_value, type_value.value)
1426                if isinstance(type_value, exp.DataType.Type)
1427                else type_value
1428            )
1429
1430        if interior:
1431            if expression.args.get("nested"):
1432                nested = f"{self.STRUCT_DELIMITER[0]}{interior}{self.STRUCT_DELIMITER[1]}"
1433                if expression.args.get("values") is not None:
1434                    delimiters = ("[", "]") if type_value == exp.DataType.Type.ARRAY else ("(", ")")
1435                    values = self.expressions(expression, key="values", flat=True)
1436                    values = f"{delimiters[0]}{values}{delimiters[1]}"
1437            elif type_value == exp.DataType.Type.INTERVAL:
1438                nested = f" {interior}"
1439            else:
1440                nested = f"({interior})"
1441
1442        type_sql = f"{type_sql}{nested}{values}"
1443        if self.TZ_TO_WITH_TIME_ZONE and type_value in (
1444            exp.DataType.Type.TIMETZ,
1445            exp.DataType.Type.TIMESTAMPTZ,
1446        ):
1447            type_sql = f"{type_sql} WITH TIME ZONE"
1448
1449        return type_sql
1450
1451    def directory_sql(self, expression: exp.Directory) -> str:
1452        local = "LOCAL " if expression.args.get("local") else ""
1453        row_format = self.sql(expression, "row_format")
1454        row_format = f" {row_format}" if row_format else ""
1455        return f"{local}DIRECTORY {self.sql(expression, 'this')}{row_format}"
1456
1457    def delete_sql(self, expression: exp.Delete) -> str:
1458        this = self.sql(expression, "this")
1459        this = f" FROM {this}" if this else ""
1460        using = self.sql(expression, "using")
1461        using = f" USING {using}" if using else ""
1462        cluster = self.sql(expression, "cluster")
1463        cluster = f" {cluster}" if cluster else ""
1464        where = self.sql(expression, "where")
1465        returning = self.sql(expression, "returning")
1466        limit = self.sql(expression, "limit")
1467        tables = self.expressions(expression, key="tables")
1468        tables = f" {tables}" if tables else ""
1469        if self.RETURNING_END:
1470            expression_sql = f"{this}{using}{cluster}{where}{returning}{limit}"
1471        else:
1472            expression_sql = f"{returning}{this}{using}{cluster}{where}{limit}"
1473        return self.prepend_ctes(expression, f"DELETE{tables}{expression_sql}")
1474
1475    def drop_sql(self, expression: exp.Drop) -> str:
1476        this = self.sql(expression, "this")
1477        expressions = self.expressions(expression, flat=True)
1478        expressions = f" ({expressions})" if expressions else ""
1479        kind = expression.args["kind"]
1480        kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1481        exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
1482        concurrently_sql = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1483        on_cluster = self.sql(expression, "cluster")
1484        on_cluster = f" {on_cluster}" if on_cluster else ""
1485        temporary = " TEMPORARY" if expression.args.get("temporary") else ""
1486        materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
1487        cascade = " CASCADE" if expression.args.get("cascade") else ""
1488        constraints = " CONSTRAINTS" if expression.args.get("constraints") else ""
1489        purge = " PURGE" if expression.args.get("purge") else ""
1490        return f"DROP{temporary}{materialized} {kind}{concurrently_sql}{exists_sql}{this}{on_cluster}{expressions}{cascade}{constraints}{purge}"
1491
1492    def set_operation(self, expression: exp.SetOperation) -> str:
1493        op_type = type(expression)
1494        op_name = op_type.key.upper()
1495
1496        distinct = expression.args.get("distinct")
1497        if (
1498            distinct is False
1499            and op_type in (exp.Except, exp.Intersect)
1500            and not self.EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE
1501        ):
1502            self.unsupported(f"{op_name} ALL is not supported")
1503
1504        default_distinct = self.dialect.SET_OP_DISTINCT_BY_DEFAULT[op_type]
1505
1506        if distinct is None:
1507            distinct = default_distinct
1508            if distinct is None:
1509                self.unsupported(f"{op_name} requires DISTINCT or ALL to be specified")
1510
1511        if distinct is default_distinct:
1512            distinct_or_all = ""
1513        else:
1514            distinct_or_all = " DISTINCT" if distinct else " ALL"
1515
1516        side_kind = " ".join(filter(None, [expression.side, expression.kind]))
1517        side_kind = f"{side_kind} " if side_kind else ""
1518
1519        by_name = " BY NAME" if expression.args.get("by_name") else ""
1520        on = self.expressions(expression, key="on", flat=True)
1521        on = f" ON ({on})" if on else ""
1522
1523        return f"{side_kind}{op_name}{distinct_or_all}{by_name}{on}"
1524
1525    def set_operations(self, expression: exp.SetOperation) -> str:
1526        if not self.SET_OP_MODIFIERS:
1527            limit = expression.args.get("limit")
1528            order = expression.args.get("order")
1529
1530            if limit or order:
1531                select = self._move_ctes_to_top_level(
1532                    exp.subquery(expression, "_l_0", copy=False).select("*", copy=False)
1533                )
1534
1535                if limit:
1536                    select = select.limit(limit.pop(), copy=False)
1537                if order:
1538                    select = select.order_by(order.pop(), copy=False)
1539                return self.sql(select)
1540
1541        sqls: t.List[str] = []
1542        stack: t.List[t.Union[str, exp.Expression]] = [expression]
1543
1544        while stack:
1545            node = stack.pop()
1546
1547            if isinstance(node, exp.SetOperation):
1548                stack.append(node.expression)
1549                stack.append(
1550                    self.maybe_comment(
1551                        self.set_operation(node), comments=node.comments, separated=True
1552                    )
1553                )
1554                stack.append(node.this)
1555            else:
1556                sqls.append(self.sql(node))
1557
1558        this = self.sep().join(sqls)
1559        this = self.query_modifiers(expression, this)
1560        return self.prepend_ctes(expression, this)
1561
1562    def fetch_sql(self, expression: exp.Fetch) -> str:
1563        direction = expression.args.get("direction")
1564        direction = f" {direction}" if direction else ""
1565        count = self.sql(expression, "count")
1566        count = f" {count}" if count else ""
1567        limit_options = self.sql(expression, "limit_options")
1568        limit_options = f"{limit_options}" if limit_options else " ROWS ONLY"
1569        return f"{self.seg('FETCH')}{direction}{count}{limit_options}"
1570
1571    def limitoptions_sql(self, expression: exp.LimitOptions) -> str:
1572        percent = " PERCENT" if expression.args.get("percent") else ""
1573        rows = " ROWS" if expression.args.get("rows") else ""
1574        with_ties = " WITH TIES" if expression.args.get("with_ties") else ""
1575        if not with_ties and rows:
1576            with_ties = " ONLY"
1577        return f"{percent}{rows}{with_ties}"
1578
1579    def filter_sql(self, expression: exp.Filter) -> str:
1580        if self.AGGREGATE_FILTER_SUPPORTED:
1581            this = self.sql(expression, "this")
1582            where = self.sql(expression, "expression").strip()
1583            return f"{this} FILTER({where})"
1584
1585        agg = expression.this
1586        agg_arg = agg.this
1587        cond = expression.expression.this
1588        agg_arg.replace(exp.If(this=cond.copy(), true=agg_arg.copy()))
1589        return self.sql(agg)
1590
1591    def hint_sql(self, expression: exp.Hint) -> str:
1592        if not self.QUERY_HINTS:
1593            self.unsupported("Hints are not supported")
1594            return ""
1595
1596        return f" /*+ {self.expressions(expression, sep=self.QUERY_HINT_SEP).strip()} */"
1597
1598    def indexparameters_sql(self, expression: exp.IndexParameters) -> str:
1599        using = self.sql(expression, "using")
1600        using = f" USING {using}" if using else ""
1601        columns = self.expressions(expression, key="columns", flat=True)
1602        columns = f"({columns})" if columns else ""
1603        partition_by = self.expressions(expression, key="partition_by", flat=True)
1604        partition_by = f" PARTITION BY {partition_by}" if partition_by else ""
1605        where = self.sql(expression, "where")
1606        include = self.expressions(expression, key="include", flat=True)
1607        if include:
1608            include = f" INCLUDE ({include})"
1609        with_storage = self.expressions(expression, key="with_storage", flat=True)
1610        with_storage = f" WITH ({with_storage})" if with_storage else ""
1611        tablespace = self.sql(expression, "tablespace")
1612        tablespace = f" USING INDEX TABLESPACE {tablespace}" if tablespace else ""
1613        on = self.sql(expression, "on")
1614        on = f" ON {on}" if on else ""
1615
1616        return f"{using}{columns}{include}{with_storage}{tablespace}{partition_by}{where}{on}"
1617
1618    def index_sql(self, expression: exp.Index) -> str:
1619        unique = "UNIQUE " if expression.args.get("unique") else ""
1620        primary = "PRIMARY " if expression.args.get("primary") else ""
1621        amp = "AMP " if expression.args.get("amp") else ""
1622        name = self.sql(expression, "this")
1623        name = f"{name} " if name else ""
1624        table = self.sql(expression, "table")
1625        table = f"{self.INDEX_ON} {table}" if table else ""
1626
1627        index = "INDEX " if not table else ""
1628
1629        params = self.sql(expression, "params")
1630        return f"{unique}{primary}{amp}{index}{name}{table}{params}"
1631
1632    def identifier_sql(self, expression: exp.Identifier) -> str:
1633        text = expression.name
1634        lower = text.lower()
1635        text = lower if self.normalize and not expression.quoted else text
1636        text = text.replace(self._identifier_end, self._escaped_identifier_end)
1637        if (
1638            expression.quoted
1639            or self.dialect.can_identify(text, self.identify)
1640            or lower in self.RESERVED_KEYWORDS
1641            or (not self.dialect.IDENTIFIERS_CAN_START_WITH_DIGIT and text[:1].isdigit())
1642        ):
1643            text = f"{self._identifier_start}{text}{self._identifier_end}"
1644        return text
1645
1646    def hex_sql(self, expression: exp.Hex) -> str:
1647        text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
1648        if self.dialect.HEX_LOWERCASE:
1649            text = self.func("LOWER", text)
1650
1651        return text
1652
1653    def lowerhex_sql(self, expression: exp.LowerHex) -> str:
1654        text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
1655        if not self.dialect.HEX_LOWERCASE:
1656            text = self.func("LOWER", text)
1657        return text
1658
1659    def inputoutputformat_sql(self, expression: exp.InputOutputFormat) -> str:
1660        input_format = self.sql(expression, "input_format")
1661        input_format = f"INPUTFORMAT {input_format}" if input_format else ""
1662        output_format = self.sql(expression, "output_format")
1663        output_format = f"OUTPUTFORMAT {output_format}" if output_format else ""
1664        return self.sep().join((input_format, output_format))
1665
1666    def national_sql(self, expression: exp.National, prefix: str = "N") -> str:
1667        string = self.sql(exp.Literal.string(expression.name))
1668        return f"{prefix}{string}"
1669
1670    def partition_sql(self, expression: exp.Partition) -> str:
1671        partition_keyword = "SUBPARTITION" if expression.args.get("subpartition") else "PARTITION"
1672        return f"{partition_keyword}({self.expressions(expression, flat=True)})"
1673
1674    def properties_sql(self, expression: exp.Properties) -> str:
1675        root_properties = []
1676        with_properties = []
1677
1678        for p in expression.expressions:
1679            p_loc = self.PROPERTIES_LOCATION[p.__class__]
1680            if p_loc == exp.Properties.Location.POST_WITH:
1681                with_properties.append(p)
1682            elif p_loc == exp.Properties.Location.POST_SCHEMA:
1683                root_properties.append(p)
1684
1685        root_props_ast = exp.Properties(expressions=root_properties)
1686        root_props_ast.parent = expression.parent
1687
1688        with_props_ast = exp.Properties(expressions=with_properties)
1689        with_props_ast.parent = expression.parent
1690
1691        root_props = self.root_properties(root_props_ast)
1692        with_props = self.with_properties(with_props_ast)
1693
1694        if root_props and with_props and not self.pretty:
1695            with_props = " " + with_props
1696
1697        return root_props + with_props
1698
1699    def root_properties(self, properties: exp.Properties) -> str:
1700        if properties.expressions:
1701            return self.expressions(properties, indent=False, sep=" ")
1702        return ""
1703
1704    def properties(
1705        self,
1706        properties: exp.Properties,
1707        prefix: str = "",
1708        sep: str = ", ",
1709        suffix: str = "",
1710        wrapped: bool = True,
1711    ) -> str:
1712        if properties.expressions:
1713            expressions = self.expressions(properties, sep=sep, indent=False)
1714            if expressions:
1715                expressions = self.wrap(expressions) if wrapped else expressions
1716                return f"{prefix}{' ' if prefix.strip() else ''}{expressions}{suffix}"
1717        return ""
1718
1719    def with_properties(self, properties: exp.Properties) -> str:
1720        return self.properties(properties, prefix=self.seg(self.WITH_PROPERTIES_PREFIX, sep=""))
1721
1722    def locate_properties(self, properties: exp.Properties) -> t.DefaultDict:
1723        properties_locs = defaultdict(list)
1724        for p in properties.expressions:
1725            p_loc = self.PROPERTIES_LOCATION[p.__class__]
1726            if p_loc != exp.Properties.Location.UNSUPPORTED:
1727                properties_locs[p_loc].append(p)
1728            else:
1729                self.unsupported(f"Unsupported property {p.key}")
1730
1731        return properties_locs
1732
1733    def property_name(self, expression: exp.Property, string_key: bool = False) -> str:
1734        if isinstance(expression.this, exp.Dot):
1735            return self.sql(expression, "this")
1736        return f"'{expression.name}'" if string_key else expression.name
1737
1738    def property_sql(self, expression: exp.Property) -> str:
1739        property_cls = expression.__class__
1740        if property_cls == exp.Property:
1741            return f"{self.property_name(expression)}={self.sql(expression, 'value')}"
1742
1743        property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls)
1744        if not property_name:
1745            self.unsupported(f"Unsupported property {expression.key}")
1746
1747        return f"{property_name}={self.sql(expression, 'this')}"
1748
1749    def likeproperty_sql(self, expression: exp.LikeProperty) -> str:
1750        if self.SUPPORTS_CREATE_TABLE_LIKE:
1751            options = " ".join(f"{e.name} {self.sql(e, 'value')}" for e in expression.expressions)
1752            options = f" {options}" if options else ""
1753
1754            like = f"LIKE {self.sql(expression, 'this')}{options}"
1755            if self.LIKE_PROPERTY_INSIDE_SCHEMA and not isinstance(expression.parent, exp.Schema):
1756                like = f"({like})"
1757
1758            return like
1759
1760        if expression.expressions:
1761            self.unsupported("Transpilation of LIKE property options is unsupported")
1762
1763        select = exp.select("*").from_(expression.this).limit(0)
1764        return f"AS {self.sql(select)}"
1765
1766    def fallbackproperty_sql(self, expression: exp.FallbackProperty) -> str:
1767        no = "NO " if expression.args.get("no") else ""
1768        protection = " PROTECTION" if expression.args.get("protection") else ""
1769        return f"{no}FALLBACK{protection}"
1770
1771    def journalproperty_sql(self, expression: exp.JournalProperty) -> str:
1772        no = "NO " if expression.args.get("no") else ""
1773        local = expression.args.get("local")
1774        local = f"{local} " if local else ""
1775        dual = "DUAL " if expression.args.get("dual") else ""
1776        before = "BEFORE " if expression.args.get("before") else ""
1777        after = "AFTER " if expression.args.get("after") else ""
1778        return f"{no}{local}{dual}{before}{after}JOURNAL"
1779
1780    def freespaceproperty_sql(self, expression: exp.FreespaceProperty) -> str:
1781        freespace = self.sql(expression, "this")
1782        percent = " PERCENT" if expression.args.get("percent") else ""
1783        return f"FREESPACE={freespace}{percent}"
1784
1785    def checksumproperty_sql(self, expression: exp.ChecksumProperty) -> str:
1786        if expression.args.get("default"):
1787            property = "DEFAULT"
1788        elif expression.args.get("on"):
1789            property = "ON"
1790        else:
1791            property = "OFF"
1792        return f"CHECKSUM={property}"
1793
1794    def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str:
1795        if expression.args.get("no"):
1796            return "NO MERGEBLOCKRATIO"
1797        if expression.args.get("default"):
1798            return "DEFAULT MERGEBLOCKRATIO"
1799
1800        percent = " PERCENT" if expression.args.get("percent") else ""
1801        return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}"
1802
1803    def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str:
1804        default = expression.args.get("default")
1805        minimum = expression.args.get("minimum")
1806        maximum = expression.args.get("maximum")
1807        if default or minimum or maximum:
1808            if default:
1809                prop = "DEFAULT"
1810            elif minimum:
1811                prop = "MINIMUM"
1812            else:
1813                prop = "MAXIMUM"
1814            return f"{prop} DATABLOCKSIZE"
1815        units = expression.args.get("units")
1816        units = f" {units}" if units else ""
1817        return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}"
1818
1819    def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str:
1820        autotemp = expression.args.get("autotemp")
1821        always = expression.args.get("always")
1822        default = expression.args.get("default")
1823        manual = expression.args.get("manual")
1824        never = expression.args.get("never")
1825
1826        if autotemp is not None:
1827            prop = f"AUTOTEMP({self.expressions(autotemp)})"
1828        elif always:
1829            prop = "ALWAYS"
1830        elif default:
1831            prop = "DEFAULT"
1832        elif manual:
1833            prop = "MANUAL"
1834        elif never:
1835            prop = "NEVER"
1836        return f"BLOCKCOMPRESSION={prop}"
1837
1838    def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str:
1839        no = expression.args.get("no")
1840        no = " NO" if no else ""
1841        concurrent = expression.args.get("concurrent")
1842        concurrent = " CONCURRENT" if concurrent else ""
1843        target = self.sql(expression, "target")
1844        target = f" {target}" if target else ""
1845        return f"WITH{no}{concurrent} ISOLATED LOADING{target}"
1846
1847    def partitionboundspec_sql(self, expression: exp.PartitionBoundSpec) -> str:
1848        if isinstance(expression.this, list):
1849            return f"IN ({self.expressions(expression, key='this', flat=True)})"
1850        if expression.this:
1851            modulus = self.sql(expression, "this")
1852            remainder = self.sql(expression, "expression")
1853            return f"WITH (MODULUS {modulus}, REMAINDER {remainder})"
1854
1855        from_expressions = self.expressions(expression, key="from_expressions", flat=True)
1856        to_expressions = self.expressions(expression, key="to_expressions", flat=True)
1857        return f"FROM ({from_expressions}) TO ({to_expressions})"
1858
1859    def partitionedofproperty_sql(self, expression: exp.PartitionedOfProperty) -> str:
1860        this = self.sql(expression, "this")
1861
1862        for_values_or_default = expression.expression
1863        if isinstance(for_values_or_default, exp.PartitionBoundSpec):
1864            for_values_or_default = f" FOR VALUES {self.sql(for_values_or_default)}"
1865        else:
1866            for_values_or_default = " DEFAULT"
1867
1868        return f"PARTITION OF {this}{for_values_or_default}"
1869
1870    def lockingproperty_sql(self, expression: exp.LockingProperty) -> str:
1871        kind = expression.args.get("kind")
1872        this = f" {self.sql(expression, 'this')}" if expression.this else ""
1873        for_or_in = expression.args.get("for_or_in")
1874        for_or_in = f" {for_or_in}" if for_or_in else ""
1875        lock_type = expression.args.get("lock_type")
1876        override = " OVERRIDE" if expression.args.get("override") else ""
1877        return f"LOCKING {kind}{this}{for_or_in} {lock_type}{override}"
1878
1879    def withdataproperty_sql(self, expression: exp.WithDataProperty) -> str:
1880        data_sql = f"WITH {'NO ' if expression.args.get('no') else ''}DATA"
1881        statistics = expression.args.get("statistics")
1882        statistics_sql = ""
1883        if statistics is not None:
1884            statistics_sql = f" AND {'NO ' if not statistics else ''}STATISTICS"
1885        return f"{data_sql}{statistics_sql}"
1886
1887    def withsystemversioningproperty_sql(self, expression: exp.WithSystemVersioningProperty) -> str:
1888        this = self.sql(expression, "this")
1889        this = f"HISTORY_TABLE={this}" if this else ""
1890        data_consistency: t.Optional[str] = self.sql(expression, "data_consistency")
1891        data_consistency = (
1892            f"DATA_CONSISTENCY_CHECK={data_consistency}" if data_consistency else None
1893        )
1894        retention_period: t.Optional[str] = self.sql(expression, "retention_period")
1895        retention_period = (
1896            f"HISTORY_RETENTION_PERIOD={retention_period}" if retention_period else None
1897        )
1898
1899        if this:
1900            on_sql = self.func("ON", this, data_consistency, retention_period)
1901        else:
1902            on_sql = "ON" if expression.args.get("on") else "OFF"
1903
1904        sql = f"SYSTEM_VERSIONING={on_sql}"
1905
1906        return f"WITH({sql})" if expression.args.get("with") else sql
1907
1908    def insert_sql(self, expression: exp.Insert) -> str:
1909        hint = self.sql(expression, "hint")
1910        overwrite = expression.args.get("overwrite")
1911
1912        if isinstance(expression.this, exp.Directory):
1913            this = " OVERWRITE" if overwrite else " INTO"
1914        else:
1915            this = self.INSERT_OVERWRITE if overwrite else " INTO"
1916
1917        stored = self.sql(expression, "stored")
1918        stored = f" {stored}" if stored else ""
1919        alternative = expression.args.get("alternative")
1920        alternative = f" OR {alternative}" if alternative else ""
1921        ignore = " IGNORE" if expression.args.get("ignore") else ""
1922        is_function = expression.args.get("is_function")
1923        if is_function:
1924            this = f"{this} FUNCTION"
1925        this = f"{this} {self.sql(expression, 'this')}"
1926
1927        exists = " IF EXISTS" if expression.args.get("exists") else ""
1928        where = self.sql(expression, "where")
1929        where = f"{self.sep()}REPLACE WHERE {where}" if where else ""
1930        expression_sql = f"{self.sep()}{self.sql(expression, 'expression')}"
1931        on_conflict = self.sql(expression, "conflict")
1932        on_conflict = f" {on_conflict}" if on_conflict else ""
1933        by_name = " BY NAME" if expression.args.get("by_name") else ""
1934        returning = self.sql(expression, "returning")
1935
1936        if self.RETURNING_END:
1937            expression_sql = f"{expression_sql}{on_conflict}{returning}"
1938        else:
1939            expression_sql = f"{returning}{expression_sql}{on_conflict}"
1940
1941        partition_by = self.sql(expression, "partition")
1942        partition_by = f" {partition_by}" if partition_by else ""
1943        settings = self.sql(expression, "settings")
1944        settings = f" {settings}" if settings else ""
1945
1946        source = self.sql(expression, "source")
1947        source = f"TABLE {source}" if source else ""
1948
1949        sql = f"INSERT{hint}{alternative}{ignore}{this}{stored}{by_name}{exists}{partition_by}{settings}{where}{expression_sql}{source}"
1950        return self.prepend_ctes(expression, sql)
1951
1952    def introducer_sql(self, expression: exp.Introducer) -> str:
1953        return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
1954
1955    def kill_sql(self, expression: exp.Kill) -> str:
1956        kind = self.sql(expression, "kind")
1957        kind = f" {kind}" if kind else ""
1958        this = self.sql(expression, "this")
1959        this = f" {this}" if this else ""
1960        return f"KILL{kind}{this}"
1961
1962    def pseudotype_sql(self, expression: exp.PseudoType) -> str:
1963        return expression.name
1964
1965    def objectidentifier_sql(self, expression: exp.ObjectIdentifier) -> str:
1966        return expression.name
1967
1968    def onconflict_sql(self, expression: exp.OnConflict) -> str:
1969        conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT"
1970
1971        constraint = self.sql(expression, "constraint")
1972        constraint = f" ON CONSTRAINT {constraint}" if constraint else ""
1973
1974        conflict_keys = self.expressions(expression, key="conflict_keys", flat=True)
1975        conflict_keys = f"({conflict_keys}) " if conflict_keys else " "
1976        action = self.sql(expression, "action")
1977
1978        expressions = self.expressions(expression, flat=True)
1979        if expressions:
1980            set_keyword = "SET " if self.DUPLICATE_KEY_UPDATE_WITH_SET else ""
1981            expressions = f" {set_keyword}{expressions}"
1982
1983        where = self.sql(expression, "where")
1984        return f"{conflict}{constraint}{conflict_keys}{action}{expressions}{where}"
1985
1986    def returning_sql(self, expression: exp.Returning) -> str:
1987        return f"{self.seg('RETURNING')} {self.expressions(expression, flat=True)}"
1988
1989    def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str:
1990        fields = self.sql(expression, "fields")
1991        fields = f" FIELDS TERMINATED BY {fields}" if fields else ""
1992        escaped = self.sql(expression, "escaped")
1993        escaped = f" ESCAPED BY {escaped}" if escaped else ""
1994        items = self.sql(expression, "collection_items")
1995        items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else ""
1996        keys = self.sql(expression, "map_keys")
1997        keys = f" MAP KEYS TERMINATED BY {keys}" if keys else ""
1998        lines = self.sql(expression, "lines")
1999        lines = f" LINES TERMINATED BY {lines}" if lines else ""
2000        null = self.sql(expression, "null")
2001        null = f" NULL DEFINED AS {null}" if null else ""
2002        return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}"
2003
2004    def withtablehint_sql(self, expression: exp.WithTableHint) -> str:
2005        return f"WITH ({self.expressions(expression, flat=True)})"
2006
2007    def indextablehint_sql(self, expression: exp.IndexTableHint) -> str:
2008        this = f"{self.sql(expression, 'this')} INDEX"
2009        target = self.sql(expression, "target")
2010        target = f" FOR {target}" if target else ""
2011        return f"{this}{target} ({self.expressions(expression, flat=True)})"
2012
2013    def historicaldata_sql(self, expression: exp.HistoricalData) -> str:
2014        this = self.sql(expression, "this")
2015        kind = self.sql(expression, "kind")
2016        expr = self.sql(expression, "expression")
2017        return f"{this} ({kind} => {expr})"
2018
2019    def table_parts(self, expression: exp.Table) -> str:
2020        return ".".join(
2021            self.sql(part)
2022            for part in (
2023                expression.args.get("catalog"),
2024                expression.args.get("db"),
2025                expression.args.get("this"),
2026            )
2027            if part is not None
2028        )
2029
2030    def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str:
2031        table = self.table_parts(expression)
2032        only = "ONLY " if expression.args.get("only") else ""
2033        partition = self.sql(expression, "partition")
2034        partition = f" {partition}" if partition else ""
2035        version = self.sql(expression, "version")
2036        version = f" {version}" if version else ""
2037        alias = self.sql(expression, "alias")
2038        alias = f"{sep}{alias}" if alias else ""
2039
2040        sample = self.sql(expression, "sample")
2041        if self.dialect.ALIAS_POST_TABLESAMPLE:
2042            sample_pre_alias = sample
2043            sample_post_alias = ""
2044        else:
2045            sample_pre_alias = ""
2046            sample_post_alias = sample
2047
2048        hints = self.expressions(expression, key="hints", sep=" ")
2049        hints = f" {hints}" if hints and self.TABLE_HINTS else ""
2050        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2051        joins = self.indent(
2052            self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
2053        )
2054        laterals = self.expressions(expression, key="laterals", sep="")
2055
2056        file_format = self.sql(expression, "format")
2057        if file_format:
2058            pattern = self.sql(expression, "pattern")
2059            pattern = f", PATTERN => {pattern}" if pattern else ""
2060            file_format = f" (FILE_FORMAT => {file_format}{pattern})"
2061
2062        ordinality = expression.args.get("ordinality") or ""
2063        if ordinality:
2064            ordinality = f" WITH ORDINALITY{alias}"
2065            alias = ""
2066
2067        when = self.sql(expression, "when")
2068        if when:
2069            table = f"{table} {when}"
2070
2071        changes = self.sql(expression, "changes")
2072        changes = f" {changes}" if changes else ""
2073
2074        rows_from = self.expressions(expression, key="rows_from")
2075        if rows_from:
2076            table = f"ROWS FROM {self.wrap(rows_from)}"
2077
2078        return f"{only}{table}{changes}{partition}{version}{file_format}{sample_pre_alias}{alias}{hints}{pivots}{sample_post_alias}{joins}{laterals}{ordinality}"
2079
2080    def tablefromrows_sql(self, expression: exp.TableFromRows) -> str:
2081        table = self.func("TABLE", expression.this)
2082        alias = self.sql(expression, "alias")
2083        alias = f" AS {alias}" if alias else ""
2084        sample = self.sql(expression, "sample")
2085        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2086        joins = self.indent(
2087            self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
2088        )
2089        return f"{table}{alias}{pivots}{sample}{joins}"
2090
2091    def tablesample_sql(
2092        self,
2093        expression: exp.TableSample,
2094        tablesample_keyword: t.Optional[str] = None,
2095    ) -> str:
2096        method = self.sql(expression, "method")
2097        method = f"{method} " if method and self.TABLESAMPLE_WITH_METHOD else ""
2098        numerator = self.sql(expression, "bucket_numerator")
2099        denominator = self.sql(expression, "bucket_denominator")
2100        field = self.sql(expression, "bucket_field")
2101        field = f" ON {field}" if field else ""
2102        bucket = f"BUCKET {numerator} OUT OF {denominator}{field}" if numerator else ""
2103        seed = self.sql(expression, "seed")
2104        seed = f" {self.TABLESAMPLE_SEED_KEYWORD} ({seed})" if seed else ""
2105
2106        size = self.sql(expression, "size")
2107        if size and self.TABLESAMPLE_SIZE_IS_ROWS:
2108            size = f"{size} ROWS"
2109
2110        percent = self.sql(expression, "percent")
2111        if percent and not self.dialect.TABLESAMPLE_SIZE_IS_PERCENT:
2112            percent = f"{percent} PERCENT"
2113
2114        expr = f"{bucket}{percent}{size}"
2115        if self.TABLESAMPLE_REQUIRES_PARENS:
2116            expr = f"({expr})"
2117
2118        return f" {tablesample_keyword or self.TABLESAMPLE_KEYWORDS} {method}{expr}{seed}"
2119
2120    def pivot_sql(self, expression: exp.Pivot) -> str:
2121        expressions = self.expressions(expression, flat=True)
2122        direction = "UNPIVOT" if expression.unpivot else "PIVOT"
2123
2124        group = self.sql(expression, "group")
2125
2126        if expression.this:
2127            this = self.sql(expression, "this")
2128            if not expressions:
2129                return f"UNPIVOT {this}"
2130
2131            on = f"{self.seg('ON')} {expressions}"
2132            into = self.sql(expression, "into")
2133            into = f"{self.seg('INTO')} {into}" if into else ""
2134            using = self.expressions(expression, key="using", flat=True)
2135            using = f"{self.seg('USING')} {using}" if using else ""
2136            return f"{direction} {this}{on}{into}{using}{group}"
2137
2138        alias = self.sql(expression, "alias")
2139        alias = f" AS {alias}" if alias else ""
2140
2141        fields = self.expressions(
2142            expression,
2143            "fields",
2144            sep=" ",
2145            dynamic=True,
2146            new_line=True,
2147            skip_first=True,
2148            skip_last=True,
2149        )
2150
2151        include_nulls = expression.args.get("include_nulls")
2152        if include_nulls is not None:
2153            nulls = " INCLUDE NULLS " if include_nulls else " EXCLUDE NULLS "
2154        else:
2155            nulls = ""
2156
2157        default_on_null = self.sql(expression, "default_on_null")
2158        default_on_null = f" DEFAULT ON NULL ({default_on_null})" if default_on_null else ""
2159        return f"{self.seg(direction)}{nulls}({expressions} FOR {fields}{default_on_null}{group}){alias}"
2160
2161    def version_sql(self, expression: exp.Version) -> str:
2162        this = f"FOR {expression.name}"
2163        kind = expression.text("kind")
2164        expr = self.sql(expression, "expression")
2165        return f"{this} {kind} {expr}"
2166
2167    def tuple_sql(self, expression: exp.Tuple) -> str:
2168        return f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
2169
2170    def update_sql(self, expression: exp.Update) -> str:
2171        this = self.sql(expression, "this")
2172        set_sql = self.expressions(expression, flat=True)
2173        from_sql = self.sql(expression, "from")
2174        where_sql = self.sql(expression, "where")
2175        returning = self.sql(expression, "returning")
2176        order = self.sql(expression, "order")
2177        limit = self.sql(expression, "limit")
2178        if self.RETURNING_END:
2179            expression_sql = f"{from_sql}{where_sql}{returning}"
2180        else:
2181            expression_sql = f"{returning}{from_sql}{where_sql}"
2182        sql = f"UPDATE {this} SET {set_sql}{expression_sql}{order}{limit}"
2183        return self.prepend_ctes(expression, sql)
2184
2185    def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str:
2186        values_as_table = values_as_table and self.VALUES_AS_TABLE
2187
2188        # The VALUES clause is still valid in an `INSERT INTO ..` statement, for example
2189        if values_as_table or not expression.find_ancestor(exp.From, exp.Join):
2190            args = self.expressions(expression)
2191            alias = self.sql(expression, "alias")
2192            values = f"VALUES{self.seg('')}{args}"
2193            values = (
2194                f"({values})"
2195                if self.WRAP_DERIVED_VALUES
2196                and (alias or isinstance(expression.parent, (exp.From, exp.Table)))
2197                else values
2198            )
2199            return f"{values} AS {alias}" if alias else values
2200
2201        # Converts `VALUES...` expression into a series of select unions.
2202        alias_node = expression.args.get("alias")
2203        column_names = alias_node and alias_node.columns
2204
2205        selects: t.List[exp.Query] = []
2206
2207        for i, tup in enumerate(expression.expressions):
2208            row = tup.expressions
2209
2210            if i == 0 and column_names:
2211                row = [
2212                    exp.alias_(value, column_name) for value, column_name in zip(row, column_names)
2213                ]
2214
2215            selects.append(exp.Select(expressions=row))
2216
2217        if self.pretty:
2218            # This may result in poor performance for large-cardinality `VALUES` tables, due to
2219            # the deep nesting of the resulting exp.Unions. If this is a problem, either increase
2220            # `sys.setrecursionlimit` to avoid RecursionErrors, or don't set `pretty`.
2221            query = reduce(lambda x, y: exp.union(x, y, distinct=False, copy=False), selects)
2222            return self.subquery_sql(query.subquery(alias_node and alias_node.this, copy=False))
2223
2224        alias = f" AS {self.sql(alias_node, 'this')}" if alias_node else ""
2225        unions = " UNION ALL ".join(self.sql(select) for select in selects)
2226        return f"({unions}){alias}"
2227
2228    def var_sql(self, expression: exp.Var) -> str:
2229        return self.sql(expression, "this")
2230
2231    @unsupported_args("expressions")
2232    def into_sql(self, expression: exp.Into) -> str:
2233        temporary = " TEMPORARY" if expression.args.get("temporary") else ""
2234        unlogged = " UNLOGGED" if expression.args.get("unlogged") else ""
2235        return f"{self.seg('INTO')}{temporary or unlogged} {self.sql(expression, 'this')}"
2236
2237    def from_sql(self, expression: exp.From) -> str:
2238        return f"{self.seg('FROM')} {self.sql(expression, 'this')}"
2239
2240    def groupingsets_sql(self, expression: exp.GroupingSets) -> str:
2241        grouping_sets = self.expressions(expression, indent=False)
2242        return f"GROUPING SETS {self.wrap(grouping_sets)}"
2243
2244    def rollup_sql(self, expression: exp.Rollup) -> str:
2245        expressions = self.expressions(expression, indent=False)
2246        return f"ROLLUP {self.wrap(expressions)}" if expressions else "WITH ROLLUP"
2247
2248    def cube_sql(self, expression: exp.Cube) -> str:
2249        expressions = self.expressions(expression, indent=False)
2250        return f"CUBE {self.wrap(expressions)}" if expressions else "WITH CUBE"
2251
2252    def group_sql(self, expression: exp.Group) -> str:
2253        group_by_all = expression.args.get("all")
2254        if group_by_all is True:
2255            modifier = " ALL"
2256        elif group_by_all is False:
2257            modifier = " DISTINCT"
2258        else:
2259            modifier = ""
2260
2261        group_by = self.op_expressions(f"GROUP BY{modifier}", expression)
2262
2263        grouping_sets = self.expressions(expression, key="grouping_sets")
2264        cube = self.expressions(expression, key="cube")
2265        rollup = self.expressions(expression, key="rollup")
2266
2267        groupings = csv(
2268            self.seg(grouping_sets) if grouping_sets else "",
2269            self.seg(cube) if cube else "",
2270            self.seg(rollup) if rollup else "",
2271            self.seg("WITH TOTALS") if expression.args.get("totals") else "",
2272            sep=self.GROUPINGS_SEP,
2273        )
2274
2275        if (
2276            expression.expressions
2277            and groupings
2278            and groupings.strip() not in ("WITH CUBE", "WITH ROLLUP")
2279        ):
2280            group_by = f"{group_by}{self.GROUPINGS_SEP}"
2281
2282        return f"{group_by}{groupings}"
2283
2284    def having_sql(self, expression: exp.Having) -> str:
2285        this = self.indent(self.sql(expression, "this"))
2286        return f"{self.seg('HAVING')}{self.sep()}{this}"
2287
2288    def connect_sql(self, expression: exp.Connect) -> str:
2289        start = self.sql(expression, "start")
2290        start = self.seg(f"START WITH {start}") if start else ""
2291        nocycle = " NOCYCLE" if expression.args.get("nocycle") else ""
2292        connect = self.sql(expression, "connect")
2293        connect = self.seg(f"CONNECT BY{nocycle} {connect}")
2294        return start + connect
2295
2296    def prior_sql(self, expression: exp.Prior) -> str:
2297        return f"PRIOR {self.sql(expression, 'this')}"
2298
2299    def join_sql(self, expression: exp.Join) -> str:
2300        if not self.SEMI_ANTI_JOIN_WITH_SIDE and expression.kind in ("SEMI", "ANTI"):
2301            side = None
2302        else:
2303            side = expression.side
2304
2305        op_sql = " ".join(
2306            op
2307            for op in (
2308                expression.method,
2309                "GLOBAL" if expression.args.get("global") else None,
2310                side,
2311                expression.kind,
2312                expression.hint if self.JOIN_HINTS else None,
2313            )
2314            if op
2315        )
2316        match_cond = self.sql(expression, "match_condition")
2317        match_cond = f" MATCH_CONDITION ({match_cond})" if match_cond else ""
2318        on_sql = self.sql(expression, "on")
2319        using = expression.args.get("using")
2320
2321        if not on_sql and using:
2322            on_sql = csv(*(self.sql(column) for column in using))
2323
2324        this = expression.this
2325        this_sql = self.sql(this)
2326
2327        exprs = self.expressions(expression)
2328        if exprs:
2329            this_sql = f"{this_sql},{self.seg(exprs)}"
2330
2331        if on_sql:
2332            on_sql = self.indent(on_sql, skip_first=True)
2333            space = self.seg(" " * self.pad) if self.pretty else " "
2334            if using:
2335                on_sql = f"{space}USING ({on_sql})"
2336            else:
2337                on_sql = f"{space}ON {on_sql}"
2338        elif not op_sql:
2339            if isinstance(this, exp.Lateral) and this.args.get("cross_apply") is not None:
2340                return f" {this_sql}"
2341
2342            return f", {this_sql}"
2343
2344        if op_sql != "STRAIGHT_JOIN":
2345            op_sql = f"{op_sql} JOIN" if op_sql else "JOIN"
2346
2347        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2348        return f"{self.seg(op_sql)} {this_sql}{match_cond}{on_sql}{pivots}"
2349
2350    def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->", wrap: bool = True) -> str:
2351        args = self.expressions(expression, flat=True)
2352        args = f"({args})" if wrap and len(args.split(",")) > 1 else args
2353        return f"{args} {arrow_sep} {self.sql(expression, 'this')}"
2354
2355    def lateral_op(self, expression: exp.Lateral) -> str:
2356        cross_apply = expression.args.get("cross_apply")
2357
2358        # https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
2359        if cross_apply is True:
2360            op = "INNER JOIN "
2361        elif cross_apply is False:
2362            op = "LEFT JOIN "
2363        else:
2364            op = ""
2365
2366        return f"{op}LATERAL"
2367
2368    def lateral_sql(self, expression: exp.Lateral) -> str:
2369        this = self.sql(expression, "this")
2370
2371        if expression.args.get("view"):
2372            alias = expression.args["alias"]
2373            columns = self.expressions(alias, key="columns", flat=True)
2374            table = f" {alias.name}" if alias.name else ""
2375            columns = f" AS {columns}" if columns else ""
2376            op_sql = self.seg(f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}")
2377            return f"{op_sql}{self.sep()}{this}{table}{columns}"
2378
2379        alias = self.sql(expression, "alias")
2380        alias = f" AS {alias}" if alias else ""
2381
2382        ordinality = expression.args.get("ordinality") or ""
2383        if ordinality:
2384            ordinality = f" WITH ORDINALITY{alias}"
2385            alias = ""
2386
2387        return f"{self.lateral_op(expression)} {this}{alias}{ordinality}"
2388
2389    def limit_sql(self, expression: exp.Limit, top: bool = False) -> str:
2390        this = self.sql(expression, "this")
2391
2392        args = [
2393            self._simplify_unless_literal(e) if self.LIMIT_ONLY_LITERALS else e
2394            for e in (expression.args.get(k) for k in ("offset", "expression"))
2395            if e
2396        ]
2397
2398        args_sql = ", ".join(self.sql(e) for e in args)
2399        args_sql = f"({args_sql})" if top and any(not e.is_number for e in args) else args_sql
2400        expressions = self.expressions(expression, flat=True)
2401        limit_options = self.sql(expression, "limit_options")
2402        expressions = f" BY {expressions}" if expressions else ""
2403
2404        return f"{this}{self.seg('TOP' if top else 'LIMIT')} {args_sql}{limit_options}{expressions}"
2405
2406    def offset_sql(self, expression: exp.Offset) -> str:
2407        this = self.sql(expression, "this")
2408        value = expression.expression
2409        value = self._simplify_unless_literal(value) if self.LIMIT_ONLY_LITERALS else value
2410        expressions = self.expressions(expression, flat=True)
2411        expressions = f" BY {expressions}" if expressions else ""
2412        return f"{this}{self.seg('OFFSET')} {self.sql(value)}{expressions}"
2413
2414    def setitem_sql(self, expression: exp.SetItem) -> str:
2415        kind = self.sql(expression, "kind")
2416        kind = f"{kind} " if kind else ""
2417        this = self.sql(expression, "this")
2418        expressions = self.expressions(expression)
2419        collate = self.sql(expression, "collate")
2420        collate = f" COLLATE {collate}" if collate else ""
2421        global_ = "GLOBAL " if expression.args.get("global") else ""
2422        return f"{global_}{kind}{this}{expressions}{collate}"
2423
2424    def set_sql(self, expression: exp.Set) -> str:
2425        expressions = f" {self.expressions(expression, flat=True)}"
2426        tag = " TAG" if expression.args.get("tag") else ""
2427        return f"{'UNSET' if expression.args.get('unset') else 'SET'}{tag}{expressions}"
2428
2429    def queryband_sql(self, expression: exp.QueryBand) -> str:
2430        this = self.sql(expression, "this")
2431        update = " UPDATE" if expression.args.get("update") else ""
2432        scope = self.sql(expression, "scope")
2433        scope = f" FOR {scope}" if scope else ""
2434
2435        return f"QUERY_BAND = {this}{update}{scope}"
2436
2437    def pragma_sql(self, expression: exp.Pragma) -> str:
2438        return f"PRAGMA {self.sql(expression, 'this')}"
2439
2440    def lock_sql(self, expression: exp.Lock) -> str:
2441        if not self.LOCKING_READS_SUPPORTED:
2442            self.unsupported("Locking reads using 'FOR UPDATE/SHARE' are not supported")
2443            return ""
2444
2445        update = expression.args["update"]
2446        key = expression.args.get("key")
2447        if update:
2448            lock_type = "FOR NO KEY UPDATE" if key else "FOR UPDATE"
2449        else:
2450            lock_type = "FOR KEY SHARE" if key else "FOR SHARE"
2451        expressions = self.expressions(expression, flat=True)
2452        expressions = f" OF {expressions}" if expressions else ""
2453        wait = expression.args.get("wait")
2454
2455        if wait is not None:
2456            if isinstance(wait, exp.Literal):
2457                wait = f" WAIT {self.sql(wait)}"
2458            else:
2459                wait = " NOWAIT" if wait else " SKIP LOCKED"
2460
2461        return f"{lock_type}{expressions}{wait or ''}"
2462
2463    def literal_sql(self, expression: exp.Literal) -> str:
2464        text = expression.this or ""
2465        if expression.is_string:
2466            text = f"{self.dialect.QUOTE_START}{self.escape_str(text)}{self.dialect.QUOTE_END}"
2467        return text
2468
2469    def escape_str(self, text: str, escape_backslash: bool = True) -> str:
2470        if self.dialect.ESCAPED_SEQUENCES:
2471            to_escaped = self.dialect.ESCAPED_SEQUENCES
2472            text = "".join(
2473                to_escaped.get(ch, ch) if escape_backslash or ch != "\\" else ch for ch in text
2474            )
2475
2476        return self._replace_line_breaks(text).replace(
2477            self.dialect.QUOTE_END, self._escaped_quote_end
2478        )
2479
2480    def loaddata_sql(self, expression: exp.LoadData) -> str:
2481        local = " LOCAL" if expression.args.get("local") else ""
2482        inpath = f" INPATH {self.sql(expression, 'inpath')}"
2483        overwrite = " OVERWRITE" if expression.args.get("overwrite") else ""
2484        this = f" INTO TABLE {self.sql(expression, 'this')}"
2485        partition = self.sql(expression, "partition")
2486        partition = f" {partition}" if partition else ""
2487        input_format = self.sql(expression, "input_format")
2488        input_format = f" INPUTFORMAT {input_format}" if input_format else ""
2489        serde = self.sql(expression, "serde")
2490        serde = f" SERDE {serde}" if serde else ""
2491        return f"LOAD DATA{local}{inpath}{overwrite}{this}{partition}{input_format}{serde}"
2492
2493    def null_sql(self, *_) -> str:
2494        return "NULL"
2495
2496    def boolean_sql(self, expression: exp.Boolean) -> str:
2497        return "TRUE" if expression.this else "FALSE"
2498
2499    def order_sql(self, expression: exp.Order, flat: bool = False) -> str:
2500        this = self.sql(expression, "this")
2501        this = f"{this} " if this else this
2502        siblings = "SIBLINGS " if expression.args.get("siblings") else ""
2503        return self.op_expressions(f"{this}ORDER {siblings}BY", expression, flat=this or flat)  # type: ignore
2504
2505    def withfill_sql(self, expression: exp.WithFill) -> str:
2506        from_sql = self.sql(expression, "from")
2507        from_sql = f" FROM {from_sql}" if from_sql else ""
2508        to_sql = self.sql(expression, "to")
2509        to_sql = f" TO {to_sql}" if to_sql else ""
2510        step_sql = self.sql(expression, "step")
2511        step_sql = f" STEP {step_sql}" if step_sql else ""
2512        interpolated_values = [
2513            f"{self.sql(e, 'alias')} AS {self.sql(e, 'this')}"
2514            if isinstance(e, exp.Alias)
2515            else self.sql(e, "this")
2516            for e in expression.args.get("interpolate") or []
2517        ]
2518        interpolate = (
2519            f" INTERPOLATE ({', '.join(interpolated_values)})" if interpolated_values else ""
2520        )
2521        return f"WITH FILL{from_sql}{to_sql}{step_sql}{interpolate}"
2522
2523    def cluster_sql(self, expression: exp.Cluster) -> str:
2524        return self.op_expressions("CLUSTER BY", expression)
2525
2526    def distribute_sql(self, expression: exp.Distribute) -> str:
2527        return self.op_expressions("DISTRIBUTE BY", expression)
2528
2529    def sort_sql(self, expression: exp.Sort) -> str:
2530        return self.op_expressions("SORT BY", expression)
2531
2532    def ordered_sql(self, expression: exp.Ordered) -> str:
2533        desc = expression.args.get("desc")
2534        asc = not desc
2535
2536        nulls_first = expression.args.get("nulls_first")
2537        nulls_last = not nulls_first
2538        nulls_are_large = self.dialect.NULL_ORDERING == "nulls_are_large"
2539        nulls_are_small = self.dialect.NULL_ORDERING == "nulls_are_small"
2540        nulls_are_last = self.dialect.NULL_ORDERING == "nulls_are_last"
2541
2542        this = self.sql(expression, "this")
2543
2544        sort_order = " DESC" if desc else (" ASC" if desc is False else "")
2545        nulls_sort_change = ""
2546        if nulls_first and (
2547            (asc and nulls_are_large) or (desc and nulls_are_small) or nulls_are_last
2548        ):
2549            nulls_sort_change = " NULLS FIRST"
2550        elif (
2551            nulls_last
2552            and ((asc and nulls_are_small) or (desc and nulls_are_large))
2553            and not nulls_are_last
2554        ):
2555            nulls_sort_change = " NULLS LAST"
2556
2557        # If the NULLS FIRST/LAST clause is unsupported, we add another sort key to simulate it
2558        if nulls_sort_change and not self.NULL_ORDERING_SUPPORTED:
2559            window = expression.find_ancestor(exp.Window, exp.Select)
2560            if isinstance(window, exp.Window) and window.args.get("spec"):
2561                self.unsupported(
2562                    f"'{nulls_sort_change.strip()}' translation not supported in window functions"
2563                )
2564                nulls_sort_change = ""
2565            elif self.NULL_ORDERING_SUPPORTED is False and (
2566                (asc and nulls_sort_change == " NULLS LAST")
2567                or (desc and nulls_sort_change == " NULLS FIRST")
2568            ):
2569                # BigQuery does not allow these ordering/nulls combinations when used under
2570                # an aggregation func or under a window containing one
2571                ancestor = expression.find_ancestor(exp.AggFunc, exp.Window, exp.Select)
2572
2573                if isinstance(ancestor, exp.Window):
2574                    ancestor = ancestor.this
2575                if isinstance(ancestor, exp.AggFunc):
2576                    self.unsupported(
2577                        f"'{nulls_sort_change.strip()}' translation not supported for aggregate functions with {sort_order} sort order"
2578                    )
2579                    nulls_sort_change = ""
2580            elif self.NULL_ORDERING_SUPPORTED is None:
2581                if expression.this.is_int:
2582                    self.unsupported(
2583                        f"'{nulls_sort_change.strip()}' translation not supported with positional ordering"
2584                    )
2585                elif not isinstance(expression.this, exp.Rand):
2586                    null_sort_order = " DESC" if nulls_sort_change == " NULLS FIRST" else ""
2587                    this = f"CASE WHEN {this} IS NULL THEN 1 ELSE 0 END{null_sort_order}, {this}"
2588                nulls_sort_change = ""
2589
2590        with_fill = self.sql(expression, "with_fill")
2591        with_fill = f" {with_fill}" if with_fill else ""
2592
2593        return f"{this}{sort_order}{nulls_sort_change}{with_fill}"
2594
2595    def matchrecognizemeasure_sql(self, expression: exp.MatchRecognizeMeasure) -> str:
2596        window_frame = self.sql(expression, "window_frame")
2597        window_frame = f"{window_frame} " if window_frame else ""
2598
2599        this = self.sql(expression, "this")
2600
2601        return f"{window_frame}{this}"
2602
2603    def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str:
2604        partition = self.partition_by_sql(expression)
2605        order = self.sql(expression, "order")
2606        measures = self.expressions(expression, key="measures")
2607        measures = self.seg(f"MEASURES{self.seg(measures)}") if measures else ""
2608        rows = self.sql(expression, "rows")
2609        rows = self.seg(rows) if rows else ""
2610        after = self.sql(expression, "after")
2611        after = self.seg(after) if after else ""
2612        pattern = self.sql(expression, "pattern")
2613        pattern = self.seg(f"PATTERN ({pattern})") if pattern else ""
2614        definition_sqls = [
2615            f"{self.sql(definition, 'alias')} AS {self.sql(definition, 'this')}"
2616            for definition in expression.args.get("define", [])
2617        ]
2618        definitions = self.expressions(sqls=definition_sqls)
2619        define = self.seg(f"DEFINE{self.seg(definitions)}") if definitions else ""
2620        body = "".join(
2621            (
2622                partition,
2623                order,
2624                measures,
2625                rows,
2626                after,
2627                pattern,
2628                define,
2629            )
2630        )
2631        alias = self.sql(expression, "alias")
2632        alias = f" {alias}" if alias else ""
2633        return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}{alias}"
2634
2635    def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str:
2636        limit = expression.args.get("limit")
2637
2638        if self.LIMIT_FETCH == "LIMIT" and isinstance(limit, exp.Fetch):
2639            limit = exp.Limit(expression=exp.maybe_copy(limit.args.get("count")))
2640        elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit):
2641            limit = exp.Fetch(direction="FIRST", count=exp.maybe_copy(limit.expression))
2642
2643        return csv(
2644            *sqls,
2645            *[self.sql(join) for join in expression.args.get("joins") or []],
2646            self.sql(expression, "match"),
2647            *[self.sql(lateral) for lateral in expression.args.get("laterals") or []],
2648            self.sql(expression, "prewhere"),
2649            self.sql(expression, "where"),
2650            self.sql(expression, "connect"),
2651            self.sql(expression, "group"),
2652            self.sql(expression, "having"),
2653            *[gen(self, expression) for gen in self.AFTER_HAVING_MODIFIER_TRANSFORMS.values()],
2654            self.sql(expression, "order"),
2655            *self.offset_limit_modifiers(expression, isinstance(limit, exp.Fetch), limit),
2656            *self.after_limit_modifiers(expression),
2657            self.options_modifier(expression),
2658            self.for_modifiers(expression),
2659            sep="",
2660        )
2661
2662    def options_modifier(self, expression: exp.Expression) -> str:
2663        options = self.expressions(expression, key="options")
2664        return f" {options}" if options else ""
2665
2666    def for_modifiers(self, expression: exp.Expression) -> str:
2667        for_modifiers = self.expressions(expression, key="for")
2668        return f"{self.sep()}FOR XML{self.seg(for_modifiers)}" if for_modifiers else ""
2669
2670    def queryoption_sql(self, expression: exp.QueryOption) -> str:
2671        self.unsupported("Unsupported query option.")
2672        return ""
2673
2674    def offset_limit_modifiers(
2675        self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit]
2676    ) -> t.List[str]:
2677        return [
2678            self.sql(expression, "offset") if fetch else self.sql(limit),
2679            self.sql(limit) if fetch else self.sql(expression, "offset"),
2680        ]
2681
2682    def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
2683        locks = self.expressions(expression, key="locks", sep=" ")
2684        locks = f" {locks}" if locks else ""
2685        return [locks, self.sql(expression, "sample")]
2686
2687    def select_sql(self, expression: exp.Select) -> str:
2688        into = expression.args.get("into")
2689        if not self.SUPPORTS_SELECT_INTO and into:
2690            into.pop()
2691
2692        hint = self.sql(expression, "hint")
2693        distinct = self.sql(expression, "distinct")
2694        distinct = f" {distinct}" if distinct else ""
2695        kind = self.sql(expression, "kind")
2696
2697        limit = expression.args.get("limit")
2698        if isinstance(limit, exp.Limit) and self.LIMIT_IS_TOP:
2699            top = self.limit_sql(limit, top=True)
2700            limit.pop()
2701        else:
2702            top = ""
2703
2704        expressions = self.expressions(expression)
2705
2706        if kind:
2707            if kind in self.SELECT_KINDS:
2708                kind = f" AS {kind}"
2709            else:
2710                if kind == "STRUCT":
2711                    expressions = self.expressions(
2712                        sqls=[
2713                            self.sql(
2714                                exp.Struct(
2715                                    expressions=[
2716                                        exp.PropertyEQ(this=e.args.get("alias"), expression=e.this)
2717                                        if isinstance(e, exp.Alias)
2718                                        else e
2719                                        for e in expression.expressions
2720                                    ]
2721                                )
2722                            )
2723                        ]
2724                    )
2725                kind = ""
2726
2727        operation_modifiers = self.expressions(expression, key="operation_modifiers", sep=" ")
2728        operation_modifiers = f"{self.sep()}{operation_modifiers}" if operation_modifiers else ""
2729
2730        # We use LIMIT_IS_TOP as a proxy for whether DISTINCT should go first because tsql and Teradata
2731        # are the only dialects that use LIMIT_IS_TOP and both place DISTINCT first.
2732        top_distinct = f"{distinct}{hint}{top}" if self.LIMIT_IS_TOP else f"{top}{hint}{distinct}"
2733        expressions = f"{self.sep()}{expressions}" if expressions else expressions
2734        sql = self.query_modifiers(
2735            expression,
2736            f"SELECT{top_distinct}{operation_modifiers}{kind}{expressions}",
2737            self.sql(expression, "into", comment=False),
2738            self.sql(expression, "from", comment=False),
2739        )
2740
2741        # If both the CTE and SELECT clauses have comments, generate the latter earlier
2742        if expression.args.get("with"):
2743            sql = self.maybe_comment(sql, expression)
2744            expression.pop_comments()
2745
2746        sql = self.prepend_ctes(expression, sql)
2747
2748        if not self.SUPPORTS_SELECT_INTO and into:
2749            if into.args.get("temporary"):
2750                table_kind = " TEMPORARY"
2751            elif self.SUPPORTS_UNLOGGED_TABLES and into.args.get("unlogged"):
2752                table_kind = " UNLOGGED"
2753            else:
2754                table_kind = ""
2755            sql = f"CREATE{table_kind} TABLE {self.sql(into.this)} AS {sql}"
2756
2757        return sql
2758
2759    def schema_sql(self, expression: exp.Schema) -> str:
2760        this = self.sql(expression, "this")
2761        sql = self.schema_columns_sql(expression)
2762        return f"{this} {sql}" if this and sql else this or sql
2763
2764    def schema_columns_sql(self, expression: exp.Schema) -> str:
2765        if expression.expressions:
2766            return f"({self.sep('')}{self.expressions(expression)}{self.seg(')', sep='')}"
2767        return ""
2768
2769    def star_sql(self, expression: exp.Star) -> str:
2770        except_ = self.expressions(expression, key="except", flat=True)
2771        except_ = f"{self.seg(self.STAR_EXCEPT)} ({except_})" if except_ else ""
2772        replace = self.expressions(expression, key="replace", flat=True)
2773        replace = f"{self.seg('REPLACE')} ({replace})" if replace else ""
2774        rename = self.expressions(expression, key="rename", flat=True)
2775        rename = f"{self.seg('RENAME')} ({rename})" if rename else ""
2776        return f"*{except_}{replace}{rename}"
2777
2778    def parameter_sql(self, expression: exp.Parameter) -> str:
2779        this = self.sql(expression, "this")
2780        return f"{self.PARAMETER_TOKEN}{this}"
2781
2782    def sessionparameter_sql(self, expression: exp.SessionParameter) -> str:
2783        this = self.sql(expression, "this")
2784        kind = expression.text("kind")
2785        if kind:
2786            kind = f"{kind}."
2787        return f"@@{kind}{this}"
2788
2789    def placeholder_sql(self, expression: exp.Placeholder) -> str:
2790        return f"{self.NAMED_PLACEHOLDER_TOKEN}{expression.name}" if expression.this else "?"
2791
2792    def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str:
2793        alias = self.sql(expression, "alias")
2794        alias = f"{sep}{alias}" if alias else ""
2795        sample = self.sql(expression, "sample")
2796        if self.dialect.ALIAS_POST_TABLESAMPLE and sample:
2797            alias = f"{sample}{alias}"
2798
2799            # Set to None so it's not generated again by self.query_modifiers()
2800            expression.set("sample", None)
2801
2802        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2803        sql = self.query_modifiers(expression, self.wrap(expression), alias, pivots)
2804        return self.prepend_ctes(expression, sql)
2805
2806    def qualify_sql(self, expression: exp.Qualify) -> str:
2807        this = self.indent(self.sql(expression, "this"))
2808        return f"{self.seg('QUALIFY')}{self.sep()}{this}"
2809
2810    def unnest_sql(self, expression: exp.Unnest) -> str:
2811        args = self.expressions(expression, flat=True)
2812
2813        alias = expression.args.get("alias")
2814        offset = expression.args.get("offset")
2815
2816        if self.UNNEST_WITH_ORDINALITY:
2817            if alias and isinstance(offset, exp.Expression):
2818                alias.append("columns", offset)
2819
2820        if alias and self.dialect.UNNEST_COLUMN_ONLY:
2821            columns = alias.columns
2822            alias = self.sql(columns[0]) if columns else ""
2823        else:
2824            alias = self.sql(alias)
2825
2826        alias = f" AS {alias}" if alias else alias
2827        if self.UNNEST_WITH_ORDINALITY:
2828            suffix = f" WITH ORDINALITY{alias}" if offset else alias
2829        else:
2830            if isinstance(offset, exp.Expression):
2831                suffix = f"{alias} WITH OFFSET AS {self.sql(offset)}"
2832            elif offset:
2833                suffix = f"{alias} WITH OFFSET"
2834            else:
2835                suffix = alias
2836
2837        return f"UNNEST({args}){suffix}"
2838
2839    def prewhere_sql(self, expression: exp.PreWhere) -> str:
2840        return ""
2841
2842    def where_sql(self, expression: exp.Where) -> str:
2843        this = self.indent(self.sql(expression, "this"))
2844        return f"{self.seg('WHERE')}{self.sep()}{this}"
2845
2846    def window_sql(self, expression: exp.Window) -> str:
2847        this = self.sql(expression, "this")
2848        partition = self.partition_by_sql(expression)
2849        order = expression.args.get("order")
2850        order = self.order_sql(order, flat=True) if order else ""
2851        spec = self.sql(expression, "spec")
2852        alias = self.sql(expression, "alias")
2853        over = self.sql(expression, "over") or "OVER"
2854
2855        this = f"{this} {'AS' if expression.arg_key == 'windows' else over}"
2856
2857        first = expression.args.get("first")
2858        if first is None:
2859            first = ""
2860        else:
2861            first = "FIRST" if first else "LAST"
2862
2863        if not partition and not order and not spec and alias:
2864            return f"{this} {alias}"
2865
2866        args = self.format_args(
2867            *[arg for arg in (alias, first, partition, order, spec) if arg], sep=" "
2868        )
2869        return f"{this} ({args})"
2870
2871    def partition_by_sql(self, expression: exp.Window | exp.MatchRecognize) -> str:
2872        partition = self.expressions(expression, key="partition_by", flat=True)
2873        return f"PARTITION BY {partition}" if partition else ""
2874
2875    def windowspec_sql(self, expression: exp.WindowSpec) -> str:
2876        kind = self.sql(expression, "kind")
2877        start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ")
2878        end = (
2879            csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ")
2880            or "CURRENT ROW"
2881        )
2882
2883        window_spec = f"{kind} BETWEEN {start} AND {end}"
2884
2885        exclude = self.sql(expression, "exclude")
2886        if exclude:
2887            if self.SUPPORTS_WINDOW_EXCLUDE:
2888                window_spec += f" EXCLUDE {exclude}"
2889            else:
2890                self.unsupported("EXCLUDE clause is not supported in the WINDOW clause")
2891
2892        return window_spec
2893
2894    def withingroup_sql(self, expression: exp.WithinGroup) -> str:
2895        this = self.sql(expression, "this")
2896        expression_sql = self.sql(expression, "expression")[1:]  # order has a leading space
2897        return f"{this} WITHIN GROUP ({expression_sql})"
2898
2899    def between_sql(self, expression: exp.Between) -> str:
2900        this = self.sql(expression, "this")
2901        low = self.sql(expression, "low")
2902        high = self.sql(expression, "high")
2903        symmetric = expression.args.get("symmetric")
2904
2905        if symmetric and not self.SUPPORTS_BETWEEN_FLAGS:
2906            return f"({this} BETWEEN {low} AND {high} OR {this} BETWEEN {high} AND {low})"
2907
2908        flag = (
2909            " SYMMETRIC"
2910            if symmetric
2911            else " ASYMMETRIC"
2912            if symmetric is False and self.SUPPORTS_BETWEEN_FLAGS
2913            else ""  # silently drop ASYMMETRIC – semantics identical
2914        )
2915        return f"{this} BETWEEN{flag} {low} AND {high}"
2916
2917    def bracket_offset_expressions(
2918        self, expression: exp.Bracket, index_offset: t.Optional[int] = None
2919    ) -> t.List[exp.Expression]:
2920        return apply_index_offset(
2921            expression.this,
2922            expression.expressions,
2923            (index_offset or self.dialect.INDEX_OFFSET) - expression.args.get("offset", 0),
2924            dialect=self.dialect,
2925        )
2926
2927    def bracket_sql(self, expression: exp.Bracket) -> str:
2928        expressions = self.bracket_offset_expressions(expression)
2929        expressions_sql = ", ".join(self.sql(e) for e in expressions)
2930        return f"{self.sql(expression, 'this')}[{expressions_sql}]"
2931
2932    def all_sql(self, expression: exp.All) -> str:
2933        this = self.sql(expression, "this")
2934        if not isinstance(expression.this, (exp.Tuple, exp.Paren)):
2935            this = self.wrap(this)
2936        return f"ALL {this}"
2937
2938    def any_sql(self, expression: exp.Any) -> str:
2939        this = self.sql(expression, "this")
2940        if isinstance(expression.this, (*exp.UNWRAPPED_QUERIES, exp.Paren)):
2941            if isinstance(expression.this, exp.UNWRAPPED_QUERIES):
2942                this = self.wrap(this)
2943            return f"ANY{this}"
2944        return f"ANY {this}"
2945
2946    def exists_sql(self, expression: exp.Exists) -> str:
2947        return f"EXISTS{self.wrap(expression)}"
2948
2949    def case_sql(self, expression: exp.Case) -> str:
2950        this = self.sql(expression, "this")
2951        statements = [f"CASE {this}" if this else "CASE"]
2952
2953        for e in expression.args["ifs"]:
2954            statements.append(f"WHEN {self.sql(e, 'this')}")
2955            statements.append(f"THEN {self.sql(e, 'true')}")
2956
2957        default = self.sql(expression, "default")
2958
2959        if default:
2960            statements.append(f"ELSE {default}")
2961
2962        statements.append("END")
2963
2964        if self.pretty and self.too_wide(statements):
2965            return self.indent("\n".join(statements), skip_first=True, skip_last=True)
2966
2967        return " ".join(statements)
2968
2969    def constraint_sql(self, expression: exp.Constraint) -> str:
2970        this = self.sql(expression, "this")
2971        expressions = self.expressions(expression, flat=True)
2972        return f"CONSTRAINT {this} {expressions}"
2973
2974    def nextvaluefor_sql(self, expression: exp.NextValueFor) -> str:
2975        order = expression.args.get("order")
2976        order = f" OVER ({self.order_sql(order, flat=True)})" if order else ""
2977        return f"NEXT VALUE FOR {self.sql(expression, 'this')}{order}"
2978
2979    def extract_sql(self, expression: exp.Extract) -> str:
2980        from sqlglot.dialects.dialect import map_date_part
2981
2982        this = (
2983            map_date_part(expression.this, self.dialect)
2984            if self.NORMALIZE_EXTRACT_DATE_PARTS
2985            else expression.this
2986        )
2987        this_sql = self.sql(this) if self.EXTRACT_ALLOWS_QUOTES else this.name
2988        expression_sql = self.sql(expression, "expression")
2989
2990        return f"EXTRACT({this_sql} FROM {expression_sql})"
2991
2992    def trim_sql(self, expression: exp.Trim) -> str:
2993        trim_type = self.sql(expression, "position")
2994
2995        if trim_type == "LEADING":
2996            func_name = "LTRIM"
2997        elif trim_type == "TRAILING":
2998            func_name = "RTRIM"
2999        else:
3000            func_name = "TRIM"
3001
3002        return self.func(func_name, expression.this, expression.expression)
3003
3004    def convert_concat_args(self, expression: exp.Concat | exp.ConcatWs) -> t.List[exp.Expression]:
3005        args = expression.expressions
3006        if isinstance(expression, exp.ConcatWs):
3007            args = args[1:]  # Skip the delimiter
3008
3009        if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
3010            args = [exp.cast(e, exp.DataType.Type.TEXT) for e in args]
3011
3012        if not self.dialect.CONCAT_COALESCE and expression.args.get("coalesce"):
3013
3014            def _wrap_with_coalesce(e: exp.Expression) -> exp.Expression:
3015                if not e.type:
3016                    from sqlglot.optimizer.annotate_types import annotate_types
3017
3018                    e = annotate_types(e, dialect=self.dialect)
3019
3020                if e.is_string or e.is_type(exp.DataType.Type.ARRAY):
3021                    return e
3022
3023                return exp.func("coalesce", e, exp.Literal.string(""))
3024
3025            args = [_wrap_with_coalesce(e) for e in args]
3026
3027        return args
3028
3029    def concat_sql(self, expression: exp.Concat) -> str:
3030        expressions = self.convert_concat_args(expression)
3031
3032        # Some dialects don't allow a single-argument CONCAT call
3033        if not self.SUPPORTS_SINGLE_ARG_CONCAT and len(expressions) == 1:
3034            return self.sql(expressions[0])
3035
3036        return self.func("CONCAT", *expressions)
3037
3038    def concatws_sql(self, expression: exp.ConcatWs) -> str:
3039        return self.func(
3040            "CONCAT_WS", seq_get(expression.expressions, 0), *self.convert_concat_args(expression)
3041        )
3042
3043    def check_sql(self, expression: exp.Check) -> str:
3044        this = self.sql(expression, key="this")
3045        return f"CHECK ({this})"
3046
3047    def foreignkey_sql(self, expression: exp.ForeignKey) -> str:
3048        expressions = self.expressions(expression, flat=True)
3049        expressions = f" ({expressions})" if expressions else ""
3050        reference = self.sql(expression, "reference")
3051        reference = f" {reference}" if reference else ""
3052        delete = self.sql(expression, "delete")
3053        delete = f" ON DELETE {delete}" if delete else ""
3054        update = self.sql(expression, "update")
3055        update = f" ON UPDATE {update}" if update else ""
3056        options = self.expressions(expression, key="options", flat=True, sep=" ")
3057        options = f" {options}" if options else ""
3058        return f"FOREIGN KEY{expressions}{reference}{delete}{update}{options}"
3059
3060    def primarykey_sql(self, expression: exp.PrimaryKey) -> str:
3061        expressions = self.expressions(expression, flat=True)
3062        include = self.sql(expression, "include")
3063        options = self.expressions(expression, key="options", flat=True, sep=" ")
3064        options = f" {options}" if options else ""
3065        return f"PRIMARY KEY ({expressions}){include}{options}"
3066
3067    def if_sql(self, expression: exp.If) -> str:
3068        return self.case_sql(exp.Case(ifs=[expression], default=expression.args.get("false")))
3069
3070    def matchagainst_sql(self, expression: exp.MatchAgainst) -> str:
3071        modifier = expression.args.get("modifier")
3072        modifier = f" {modifier}" if modifier else ""
3073        return f"{self.func('MATCH', *expression.expressions)} AGAINST({self.sql(expression, 'this')}{modifier})"
3074
3075    def jsonkeyvalue_sql(self, expression: exp.JSONKeyValue) -> str:
3076        return f"{self.sql(expression, 'this')}{self.JSON_KEY_VALUE_PAIR_SEP} {self.sql(expression, 'expression')}"
3077
3078    def jsonpath_sql(self, expression: exp.JSONPath) -> str:
3079        path = self.expressions(expression, sep="", flat=True).lstrip(".")
3080
3081        if expression.args.get("escape"):
3082            path = self.escape_str(path)
3083
3084        if self.QUOTE_JSON_PATH:
3085            path = f"{self.dialect.QUOTE_START}{path}{self.dialect.QUOTE_END}"
3086
3087        return path
3088
3089    def json_path_part(self, expression: int | str | exp.JSONPathPart) -> str:
3090        if isinstance(expression, exp.JSONPathPart):
3091            transform = self.TRANSFORMS.get(expression.__class__)
3092            if not callable(transform):
3093                self.unsupported(f"Unsupported JSONPathPart type {expression.__class__.__name__}")
3094                return ""
3095
3096            return transform(self, expression)
3097
3098        if isinstance(expression, int):
3099            return str(expression)
3100
3101        if self._quote_json_path_key_using_brackets and self.JSON_PATH_SINGLE_QUOTE_ESCAPE:
3102            escaped = expression.replace("'", "\\'")
3103            escaped = f"\\'{expression}\\'"
3104        else:
3105            escaped = expression.replace('"', '\\"')
3106            escaped = f'"{escaped}"'
3107
3108        return escaped
3109
3110    def formatjson_sql(self, expression: exp.FormatJson) -> str:
3111        return f"{self.sql(expression, 'this')} FORMAT JSON"
3112
3113    def formatphrase_sql(self, expression: exp.FormatPhrase) -> str:
3114        # Output the Teradata column FORMAT override.
3115        # https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Data-Types-and-Literals/Data-Type-Formats-and-Format-Phrases/FORMAT
3116        this = self.sql(expression, "this")
3117        fmt = self.sql(expression, "format")
3118        return f"{this} (FORMAT {fmt})"
3119
3120    def jsonobject_sql(self, expression: exp.JSONObject | exp.JSONObjectAgg) -> str:
3121        null_handling = expression.args.get("null_handling")
3122        null_handling = f" {null_handling}" if null_handling else ""
3123
3124        unique_keys = expression.args.get("unique_keys")
3125        if unique_keys is not None:
3126            unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS"
3127        else:
3128            unique_keys = ""
3129
3130        return_type = self.sql(expression, "return_type")
3131        return_type = f" RETURNING {return_type}" if return_type else ""
3132        encoding = self.sql(expression, "encoding")
3133        encoding = f" ENCODING {encoding}" if encoding else ""
3134
3135        return self.func(
3136            "JSON_OBJECT" if isinstance(expression, exp.JSONObject) else "JSON_OBJECTAGG",
3137            *expression.expressions,
3138            suffix=f"{null_handling}{unique_keys}{return_type}{encoding})",
3139        )
3140
3141    def jsonobjectagg_sql(self, expression: exp.JSONObjectAgg) -> str:
3142        return self.jsonobject_sql(expression)
3143
3144    def jsonarray_sql(self, expression: exp.JSONArray) -> str:
3145        null_handling = expression.args.get("null_handling")
3146        null_handling = f" {null_handling}" if null_handling else ""
3147        return_type = self.sql(expression, "return_type")
3148        return_type = f" RETURNING {return_type}" if return_type else ""
3149        strict = " STRICT" if expression.args.get("strict") else ""
3150        return self.func(
3151            "JSON_ARRAY", *expression.expressions, suffix=f"{null_handling}{return_type}{strict})"
3152        )
3153
3154    def jsonarrayagg_sql(self, expression: exp.JSONArrayAgg) -> str:
3155        this = self.sql(expression, "this")
3156        order = self.sql(expression, "order")
3157        null_handling = expression.args.get("null_handling")
3158        null_handling = f" {null_handling}" if null_handling else ""
3159        return_type = self.sql(expression, "return_type")
3160        return_type = f" RETURNING {return_type}" if return_type else ""
3161        strict = " STRICT" if expression.args.get("strict") else ""
3162        return self.func(
3163            "JSON_ARRAYAGG",
3164            this,
3165            suffix=f"{order}{null_handling}{return_type}{strict})",
3166        )
3167
3168    def jsoncolumndef_sql(self, expression: exp.JSONColumnDef) -> str:
3169        path = self.sql(expression, "path")
3170        path = f" PATH {path}" if path else ""
3171        nested_schema = self.sql(expression, "nested_schema")
3172
3173        if nested_schema:
3174            return f"NESTED{path} {nested_schema}"
3175
3176        this = self.sql(expression, "this")
3177        kind = self.sql(expression, "kind")
3178        kind = f" {kind}" if kind else ""
3179        return f"{this}{kind}{path}"
3180
3181    def jsonschema_sql(self, expression: exp.JSONSchema) -> str:
3182        return self.func("COLUMNS", *expression.expressions)
3183
3184    def jsontable_sql(self, expression: exp.JSONTable) -> str:
3185        this = self.sql(expression, "this")
3186        path = self.sql(expression, "path")
3187        path = f", {path}" if path else ""
3188        error_handling = expression.args.get("error_handling")
3189        error_handling = f" {error_handling}" if error_handling else ""
3190        empty_handling = expression.args.get("empty_handling")
3191        empty_handling = f" {empty_handling}" if empty_handling else ""
3192        schema = self.sql(expression, "schema")
3193        return self.func(
3194            "JSON_TABLE", this, suffix=f"{path}{error_handling}{empty_handling} {schema})"
3195        )
3196
3197    def openjsoncolumndef_sql(self, expression: exp.OpenJSONColumnDef) -> str:
3198        this = self.sql(expression, "this")
3199        kind = self.sql(expression, "kind")
3200        path = self.sql(expression, "path")
3201        path = f" {path}" if path else ""
3202        as_json = " AS JSON" if expression.args.get("as_json") else ""
3203        return f"{this} {kind}{path}{as_json}"
3204
3205    def openjson_sql(self, expression: exp.OpenJSON) -> str:
3206        this = self.sql(expression, "this")
3207        path = self.sql(expression, "path")
3208        path = f", {path}" if path else ""
3209        expressions = self.expressions(expression)
3210        with_ = (
3211            f" WITH ({self.seg(self.indent(expressions), sep='')}{self.seg(')', sep='')}"
3212            if expressions
3213            else ""
3214        )
3215        return f"OPENJSON({this}{path}){with_}"
3216
3217    def in_sql(self, expression: exp.In) -> str:
3218        query = expression.args.get("query")
3219        unnest = expression.args.get("unnest")
3220        field = expression.args.get("field")
3221        is_global = " GLOBAL" if expression.args.get("is_global") else ""
3222
3223        if query:
3224            in_sql = self.sql(query)
3225        elif unnest:
3226            in_sql = self.in_unnest_op(unnest)
3227        elif field:
3228            in_sql = self.sql(field)
3229        else:
3230            in_sql = f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
3231
3232        return f"{self.sql(expression, 'this')}{is_global} IN {in_sql}"
3233
3234    def in_unnest_op(self, unnest: exp.Unnest) -> str:
3235        return f"(SELECT {self.sql(unnest)})"
3236
3237    def interval_sql(self, expression: exp.Interval) -> str:
3238        unit = self.sql(expression, "unit")
3239        if not self.INTERVAL_ALLOWS_PLURAL_FORM:
3240            unit = self.TIME_PART_SINGULARS.get(unit, unit)
3241        unit = f" {unit}" if unit else ""
3242
3243        if self.SINGLE_STRING_INTERVAL:
3244            this = expression.this.name if expression.this else ""
3245            return f"INTERVAL '{this}{unit}'" if this else f"INTERVAL{unit}"
3246
3247        this = self.sql(expression, "this")
3248        if this:
3249            unwrapped = isinstance(expression.this, self.UNWRAPPED_INTERVAL_VALUES)
3250            this = f" {this}" if unwrapped else f" ({this})"
3251
3252        return f"INTERVAL{this}{unit}"
3253
3254    def return_sql(self, expression: exp.Return) -> str:
3255        return f"RETURN {self.sql(expression, 'this')}"
3256
3257    def reference_sql(self, expression: exp.Reference) -> str:
3258        this = self.sql(expression, "this")
3259        expressions = self.expressions(expression, flat=True)
3260        expressions = f"({expressions})" if expressions else ""
3261        options = self.expressions(expression, key="options", flat=True, sep=" ")
3262        options = f" {options}" if options else ""
3263        return f"REFERENCES {this}{expressions}{options}"
3264
3265    def anonymous_sql(self, expression: exp.Anonymous) -> str:
3266        # We don't normalize qualified functions such as a.b.foo(), because they can be case-sensitive
3267        parent = expression.parent
3268        is_qualified = isinstance(parent, exp.Dot) and expression is parent.expression
3269        return self.func(
3270            self.sql(expression, "this"), *expression.expressions, normalize=not is_qualified
3271        )
3272
3273    def paren_sql(self, expression: exp.Paren) -> str:
3274        sql = self.seg(self.indent(self.sql(expression, "this")), sep="")
3275        return f"({sql}{self.seg(')', sep='')}"
3276
3277    def neg_sql(self, expression: exp.Neg) -> str:
3278        # This makes sure we don't convert "- - 5" to "--5", which is a comment
3279        this_sql = self.sql(expression, "this")
3280        sep = " " if this_sql[0] == "-" else ""
3281        return f"-{sep}{this_sql}"
3282
3283    def not_sql(self, expression: exp.Not) -> str:
3284        return f"NOT {self.sql(expression, 'this')}"
3285
3286    def alias_sql(self, expression: exp.Alias) -> str:
3287        alias = self.sql(expression, "alias")
3288        alias = f" AS {alias}" if alias else ""
3289        return f"{self.sql(expression, 'this')}{alias}"
3290
3291    def pivotalias_sql(self, expression: exp.PivotAlias) -> str:
3292        alias = expression.args["alias"]
3293
3294        parent = expression.parent
3295        pivot = parent and parent.parent
3296
3297        if isinstance(pivot, exp.Pivot) and pivot.unpivot:
3298            identifier_alias = isinstance(alias, exp.Identifier)
3299            literal_alias = isinstance(alias, exp.Literal)
3300
3301            if identifier_alias and not self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
3302                alias.replace(exp.Literal.string(alias.output_name))
3303            elif not identifier_alias and literal_alias and self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
3304                alias.replace(exp.to_identifier(alias.output_name))
3305
3306        return self.alias_sql(expression)
3307
3308    def aliases_sql(self, expression: exp.Aliases) -> str:
3309        return f"{self.sql(expression, 'this')} AS ({self.expressions(expression, flat=True)})"
3310
3311    def atindex_sql(self, expression: exp.AtTimeZone) -> str:
3312        this = self.sql(expression, "this")
3313        index = self.sql(expression, "expression")
3314        return f"{this} AT {index}"
3315
3316    def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
3317        this = self.sql(expression, "this")
3318        zone = self.sql(expression, "zone")
3319        return f"{this} AT TIME ZONE {zone}"
3320
3321    def fromtimezone_sql(self, expression: exp.FromTimeZone) -> str:
3322        this = self.sql(expression, "this")
3323        zone = self.sql(expression, "zone")
3324        return f"{this} AT TIME ZONE {zone} AT TIME ZONE 'UTC'"
3325
3326    def add_sql(self, expression: exp.Add) -> str:
3327        return self.binary(expression, "+")
3328
3329    def and_sql(
3330        self, expression: exp.And, stack: t.Optional[t.List[str | exp.Expression]] = None
3331    ) -> str:
3332        return self.connector_sql(expression, "AND", stack)
3333
3334    def or_sql(
3335        self, expression: exp.Or, stack: t.Optional[t.List[str | exp.Expression]] = None
3336    ) -> str:
3337        return self.connector_sql(expression, "OR", stack)
3338
3339    def xor_sql(
3340        self, expression: exp.Xor, stack: t.Optional[t.List[str | exp.Expression]] = None
3341    ) -> str:
3342        return self.connector_sql(expression, "XOR", stack)
3343
3344    def connector_sql(
3345        self,
3346        expression: exp.Connector,
3347        op: str,
3348        stack: t.Optional[t.List[str | exp.Expression]] = None,
3349    ) -> str:
3350        if stack is not None:
3351            if expression.expressions:
3352                stack.append(self.expressions(expression, sep=f" {op} "))
3353            else:
3354                stack.append(expression.right)
3355                if expression.comments and self.comments:
3356                    for comment in expression.comments:
3357                        if comment:
3358                            op += f" /*{self.sanitize_comment(comment)}*/"
3359                stack.extend((op, expression.left))
3360            return op
3361
3362        stack = [expression]
3363        sqls: t.List[str] = []
3364        ops = set()
3365
3366        while stack:
3367            node = stack.pop()
3368            if isinstance(node, exp.Connector):
3369                ops.add(getattr(self, f"{node.key}_sql")(node, stack))
3370            else:
3371                sql = self.sql(node)
3372                if sqls and sqls[-1] in ops:
3373                    sqls[-1] += f" {sql}"
3374                else:
3375                    sqls.append(sql)
3376
3377        sep = "\n" if self.pretty and self.too_wide(sqls) else " "
3378        return sep.join(sqls)
3379
3380    def bitwiseand_sql(self, expression: exp.BitwiseAnd) -> str:
3381        return self.binary(expression, "&")
3382
3383    def bitwiseleftshift_sql(self, expression: exp.BitwiseLeftShift) -> str:
3384        return self.binary(expression, "<<")
3385
3386    def bitwisenot_sql(self, expression: exp.BitwiseNot) -> str:
3387        return f"~{self.sql(expression, 'this')}"
3388
3389    def bitwiseor_sql(self, expression: exp.BitwiseOr) -> str:
3390        return self.binary(expression, "|")
3391
3392    def bitwiserightshift_sql(self, expression: exp.BitwiseRightShift) -> str:
3393        return self.binary(expression, ">>")
3394
3395    def bitwisexor_sql(self, expression: exp.BitwiseXor) -> str:
3396        return self.binary(expression, "^")
3397
3398    def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
3399        format_sql = self.sql(expression, "format")
3400        format_sql = f" FORMAT {format_sql}" if format_sql else ""
3401        to_sql = self.sql(expression, "to")
3402        to_sql = f" {to_sql}" if to_sql else ""
3403        action = self.sql(expression, "action")
3404        action = f" {action}" if action else ""
3405        default = self.sql(expression, "default")
3406        default = f" DEFAULT {default} ON CONVERSION ERROR" if default else ""
3407        return f"{safe_prefix or ''}CAST({self.sql(expression, 'this')} AS{to_sql}{default}{format_sql}{action})"
3408
3409    def currentdate_sql(self, expression: exp.CurrentDate) -> str:
3410        zone = self.sql(expression, "this")
3411        return f"CURRENT_DATE({zone})" if zone else "CURRENT_DATE"
3412
3413    def collate_sql(self, expression: exp.Collate) -> str:
3414        if self.COLLATE_IS_FUNC:
3415            return self.function_fallback_sql(expression)
3416        return self.binary(expression, "COLLATE")
3417
3418    def command_sql(self, expression: exp.Command) -> str:
3419        return f"{self.sql(expression, 'this')} {expression.text('expression').strip()}"
3420
3421    def comment_sql(self, expression: exp.Comment) -> str:
3422        this = self.sql(expression, "this")
3423        kind = expression.args["kind"]
3424        materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
3425        exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
3426        expression_sql = self.sql(expression, "expression")
3427        return f"COMMENT{exists_sql}ON{materialized} {kind} {this} IS {expression_sql}"
3428
3429    def mergetreettlaction_sql(self, expression: exp.MergeTreeTTLAction) -> str:
3430        this = self.sql(expression, "this")
3431        delete = " DELETE" if expression.args.get("delete") else ""
3432        recompress = self.sql(expression, "recompress")
3433        recompress = f" RECOMPRESS {recompress}" if recompress else ""
3434        to_disk = self.sql(expression, "to_disk")
3435        to_disk = f" TO DISK {to_disk}" if to_disk else ""
3436        to_volume = self.sql(expression, "to_volume")
3437        to_volume = f" TO VOLUME {to_volume}" if to_volume else ""
3438        return f"{this}{delete}{recompress}{to_disk}{to_volume}"
3439
3440    def mergetreettl_sql(self, expression: exp.MergeTreeTTL) -> str:
3441        where = self.sql(expression, "where")
3442        group = self.sql(expression, "group")
3443        aggregates = self.expressions(expression, key="aggregates")
3444        aggregates = self.seg("SET") + self.seg(aggregates) if aggregates else ""
3445
3446        if not (where or group or aggregates) and len(expression.expressions) == 1:
3447            return f"TTL {self.expressions(expression, flat=True)}"
3448
3449        return f"TTL{self.seg(self.expressions(expression))}{where}{group}{aggregates}"
3450
3451    def transaction_sql(self, expression: exp.Transaction) -> str:
3452        return "BEGIN"
3453
3454    def commit_sql(self, expression: exp.Commit) -> str:
3455        chain = expression.args.get("chain")
3456        if chain is not None:
3457            chain = " AND CHAIN" if chain else " AND NO CHAIN"
3458
3459        return f"COMMIT{chain or ''}"
3460
3461    def rollback_sql(self, expression: exp.Rollback) -> str:
3462        savepoint = expression.args.get("savepoint")
3463        savepoint = f" TO {savepoint}" if savepoint else ""
3464        return f"ROLLBACK{savepoint}"
3465
3466    def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
3467        this = self.sql(expression, "this")
3468
3469        dtype = self.sql(expression, "dtype")
3470        if dtype:
3471            collate = self.sql(expression, "collate")
3472            collate = f" COLLATE {collate}" if collate else ""
3473            using = self.sql(expression, "using")
3474            using = f" USING {using}" if using else ""
3475            alter_set_type = self.ALTER_SET_TYPE + " " if self.ALTER_SET_TYPE else ""
3476            return f"ALTER COLUMN {this} {alter_set_type}{dtype}{collate}{using}"
3477
3478        default = self.sql(expression, "default")
3479        if default:
3480            return f"ALTER COLUMN {this} SET DEFAULT {default}"
3481
3482        comment = self.sql(expression, "comment")
3483        if comment:
3484            return f"ALTER COLUMN {this} COMMENT {comment}"
3485
3486        visible = expression.args.get("visible")
3487        if visible:
3488            return f"ALTER COLUMN {this} SET {visible}"
3489
3490        allow_null = expression.args.get("allow_null")
3491        drop = expression.args.get("drop")
3492
3493        if not drop and not allow_null:
3494            self.unsupported("Unsupported ALTER COLUMN syntax")
3495
3496        if allow_null is not None:
3497            keyword = "DROP" if drop else "SET"
3498            return f"ALTER COLUMN {this} {keyword} NOT NULL"
3499
3500        return f"ALTER COLUMN {this} DROP DEFAULT"
3501
3502    def alterindex_sql(self, expression: exp.AlterIndex) -> str:
3503        this = self.sql(expression, "this")
3504
3505        visible = expression.args.get("visible")
3506        visible_sql = "VISIBLE" if visible else "INVISIBLE"
3507
3508        return f"ALTER INDEX {this} {visible_sql}"
3509
3510    def alterdiststyle_sql(self, expression: exp.AlterDistStyle) -> str:
3511        this = self.sql(expression, "this")
3512        if not isinstance(expression.this, exp.Var):
3513            this = f"KEY DISTKEY {this}"
3514        return f"ALTER DISTSTYLE {this}"
3515
3516    def altersortkey_sql(self, expression: exp.AlterSortKey) -> str:
3517        compound = " COMPOUND" if expression.args.get("compound") else ""
3518        this = self.sql(expression, "this")
3519        expressions = self.expressions(expression, flat=True)
3520        expressions = f"({expressions})" if expressions else ""
3521        return f"ALTER{compound} SORTKEY {this or expressions}"
3522
3523    def alterrename_sql(self, expression: exp.AlterRename, include_to: bool = True) -> str:
3524        if not self.RENAME_TABLE_WITH_DB:
3525            # Remove db from tables
3526            expression = expression.transform(
3527                lambda n: exp.table_(n.this) if isinstance(n, exp.Table) else n
3528            ).assert_is(exp.AlterRename)
3529        this = self.sql(expression, "this")
3530        to_kw = " TO" if include_to else ""
3531        return f"RENAME{to_kw} {this}"
3532
3533    def renamecolumn_sql(self, expression: exp.RenameColumn) -> str:
3534        exists = " IF EXISTS" if expression.args.get("exists") else ""
3535        old_column = self.sql(expression, "this")
3536        new_column = self.sql(expression, "to")
3537        return f"RENAME COLUMN{exists} {old_column} TO {new_column}"
3538
3539    def alterset_sql(self, expression: exp.AlterSet) -> str:
3540        exprs = self.expressions(expression, flat=True)
3541        if self.ALTER_SET_WRAPPED:
3542            exprs = f"({exprs})"
3543
3544        return f"SET {exprs}"
3545
3546    def alter_sql(self, expression: exp.Alter) -> str:
3547        actions = expression.args["actions"]
3548
3549        if not self.dialect.ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN and isinstance(
3550            actions[0], exp.ColumnDef
3551        ):
3552            actions_sql = self.expressions(expression, key="actions", flat=True)
3553            actions_sql = f"ADD {actions_sql}"
3554        else:
3555            actions_list = []
3556            for action in actions:
3557                if isinstance(action, (exp.ColumnDef, exp.Schema)):
3558                    action_sql = self.add_column_sql(action)
3559                else:
3560                    action_sql = self.sql(action)
3561                    if isinstance(action, exp.Query):
3562                        action_sql = f"AS {action_sql}"
3563
3564                actions_list.append(action_sql)
3565
3566            actions_sql = self.format_args(*actions_list).lstrip("\n")
3567
3568        exists = " IF EXISTS" if expression.args.get("exists") else ""
3569        on_cluster = self.sql(expression, "cluster")
3570        on_cluster = f" {on_cluster}" if on_cluster else ""
3571        only = " ONLY" if expression.args.get("only") else ""
3572        options = self.expressions(expression, key="options")
3573        options = f", {options}" if options else ""
3574        kind = self.sql(expression, "kind")
3575        not_valid = " NOT VALID" if expression.args.get("not_valid") else ""
3576        check = " WITH CHECK" if expression.args.get("check") else ""
3577        this = self.sql(expression, "this")
3578        this = f" {this}" if this else ""
3579
3580        return f"ALTER {kind}{exists}{only}{this}{on_cluster}{check}{self.sep()}{actions_sql}{not_valid}{options}"
3581
3582    def altersession_sql(self, expression: exp.AlterSession) -> str:
3583        items_sql = self.expressions(expression, flat=True)
3584        keyword = "UNSET" if expression.args.get("unset") else "SET"
3585        return f"{keyword} {items_sql}"
3586
3587    def add_column_sql(self, expression: exp.Expression) -> str:
3588        sql = self.sql(expression)
3589        if isinstance(expression, exp.Schema):
3590            column_text = " COLUMNS"
3591        elif isinstance(expression, exp.ColumnDef) and self.ALTER_TABLE_INCLUDE_COLUMN_KEYWORD:
3592            column_text = " COLUMN"
3593        else:
3594            column_text = ""
3595
3596        return f"ADD{column_text} {sql}"
3597
3598    def droppartition_sql(self, expression: exp.DropPartition) -> str:
3599        expressions = self.expressions(expression)
3600        exists = " IF EXISTS " if expression.args.get("exists") else " "
3601        return f"DROP{exists}{expressions}"
3602
3603    def addconstraint_sql(self, expression: exp.AddConstraint) -> str:
3604        return f"ADD {self.expressions(expression, indent=False)}"
3605
3606    def addpartition_sql(self, expression: exp.AddPartition) -> str:
3607        exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
3608        location = self.sql(expression, "location")
3609        location = f" {location}" if location else ""
3610        return f"ADD {exists}{self.sql(expression.this)}{location}"
3611
3612    def distinct_sql(self, expression: exp.Distinct) -> str:
3613        this = self.expressions(expression, flat=True)
3614
3615        if not self.MULTI_ARG_DISTINCT and len(expression.expressions) > 1:
3616            case = exp.case()
3617            for arg in expression.expressions:
3618                case = case.when(arg.is_(exp.null()), exp.null())
3619            this = self.sql(case.else_(f"({this})"))
3620
3621        this = f" {this}" if this else ""
3622
3623        on = self.sql(expression, "on")
3624        on = f" ON {on}" if on else ""
3625        return f"DISTINCT{this}{on}"
3626
3627    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
3628        return self._embed_ignore_nulls(expression, "IGNORE NULLS")
3629
3630    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
3631        return self._embed_ignore_nulls(expression, "RESPECT NULLS")
3632
3633    def havingmax_sql(self, expression: exp.HavingMax) -> str:
3634        this_sql = self.sql(expression, "this")
3635        expression_sql = self.sql(expression, "expression")
3636        kind = "MAX" if expression.args.get("max") else "MIN"
3637        return f"{this_sql} HAVING {kind} {expression_sql}"
3638
3639    def intdiv_sql(self, expression: exp.IntDiv) -> str:
3640        return self.sql(
3641            exp.Cast(
3642                this=exp.Div(this=expression.this, expression=expression.expression),
3643                to=exp.DataType(this=exp.DataType.Type.INT),
3644            )
3645        )
3646
3647    def dpipe_sql(self, expression: exp.DPipe) -> str:
3648        if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
3649            return self.func(
3650                "CONCAT", *(exp.cast(e, exp.DataType.Type.TEXT) for e in expression.flatten())
3651            )
3652        return self.binary(expression, "||")
3653
3654    def div_sql(self, expression: exp.Div) -> str:
3655        l, r = expression.left, expression.right
3656
3657        if not self.dialect.SAFE_DIVISION and expression.args.get("safe"):
3658            r.replace(exp.Nullif(this=r.copy(), expression=exp.Literal.number(0)))
3659
3660        if self.dialect.TYPED_DIVISION and not expression.args.get("typed"):
3661            if not l.is_type(*exp.DataType.REAL_TYPES) and not r.is_type(*exp.DataType.REAL_TYPES):
3662                l.replace(exp.cast(l.copy(), to=exp.DataType.Type.DOUBLE))
3663
3664        elif not self.dialect.TYPED_DIVISION and expression.args.get("typed"):
3665            if l.is_type(*exp.DataType.INTEGER_TYPES) and r.is_type(*exp.DataType.INTEGER_TYPES):
3666                return self.sql(
3667                    exp.cast(
3668                        l / r,
3669                        to=exp.DataType.Type.BIGINT,
3670                    )
3671                )
3672
3673        return self.binary(expression, "/")
3674
3675    def safedivide_sql(self, expression: exp.SafeDivide) -> str:
3676        n = exp._wrap(expression.this, exp.Binary)
3677        d = exp._wrap(expression.expression, exp.Binary)
3678        return self.sql(exp.If(this=d.neq(0), true=n / d, false=exp.Null()))
3679
3680    def overlaps_sql(self, expression: exp.Overlaps) -> str:
3681        return self.binary(expression, "OVERLAPS")
3682
3683    def distance_sql(self, expression: exp.Distance) -> str:
3684        return self.binary(expression, "<->")
3685
3686    def dot_sql(self, expression: exp.Dot) -> str:
3687        return f"{self.sql(expression, 'this')}.{self.sql(expression, 'expression')}"
3688
3689    def eq_sql(self, expression: exp.EQ) -> str:
3690        return self.binary(expression, "=")
3691
3692    def propertyeq_sql(self, expression: exp.PropertyEQ) -> str:
3693        return self.binary(expression, ":=")
3694
3695    def escape_sql(self, expression: exp.Escape) -> str:
3696        return self.binary(expression, "ESCAPE")
3697
3698    def glob_sql(self, expression: exp.Glob) -> str:
3699        return self.binary(expression, "GLOB")
3700
3701    def gt_sql(self, expression: exp.GT) -> str:
3702        return self.binary(expression, ">")
3703
3704    def gte_sql(self, expression: exp.GTE) -> str:
3705        return self.binary(expression, ">=")
3706
3707    def is_sql(self, expression: exp.Is) -> str:
3708        if not self.IS_BOOL_ALLOWED and isinstance(expression.expression, exp.Boolean):
3709            return self.sql(
3710                expression.this if expression.expression.this else exp.not_(expression.this)
3711            )
3712        return self.binary(expression, "IS")
3713
3714    def _like_sql(self, expression: exp.Like | exp.ILike) -> str:
3715        this = expression.this
3716        rhs = expression.expression
3717
3718        if isinstance(expression, exp.Like):
3719            exp_class: t.Type[exp.Like | exp.ILike] = exp.Like
3720            op = "LIKE"
3721        else:
3722            exp_class = exp.ILike
3723            op = "ILIKE"
3724
3725        if isinstance(rhs, (exp.All, exp.Any)) and not self.SUPPORTS_LIKE_QUANTIFIERS:
3726            exprs = rhs.this.unnest()
3727
3728            if isinstance(exprs, exp.Tuple):
3729                exprs = exprs.expressions
3730
3731            connective = exp.or_ if isinstance(rhs, exp.Any) else exp.and_
3732
3733            like_expr: exp.Expression = exp_class(this=this, expression=exprs[0])
3734            for expr in exprs[1:]:
3735                like_expr = connective(like_expr, exp_class(this=this, expression=expr))
3736
3737            return self.sql(like_expr)
3738
3739        return self.binary(expression, op)
3740
3741    def like_sql(self, expression: exp.Like) -> str:
3742        return self._like_sql(expression)
3743
3744    def ilike_sql(self, expression: exp.ILike) -> str:
3745        return self._like_sql(expression)
3746
3747    def similarto_sql(self, expression: exp.SimilarTo) -> str:
3748        return self.binary(expression, "SIMILAR TO")
3749
3750    def lt_sql(self, expression: exp.LT) -> str:
3751        return self.binary(expression, "<")
3752
3753    def lte_sql(self, expression: exp.LTE) -> str:
3754        return self.binary(expression, "<=")
3755
3756    def mod_sql(self, expression: exp.Mod) -> str:
3757        return self.binary(expression, "%")
3758
3759    def mul_sql(self, expression: exp.Mul) -> str:
3760        return self.binary(expression, "*")
3761
3762    def neq_sql(self, expression: exp.NEQ) -> str:
3763        return self.binary(expression, "<>")
3764
3765    def nullsafeeq_sql(self, expression: exp.NullSafeEQ) -> str:
3766        return self.binary(expression, "IS NOT DISTINCT FROM")
3767
3768    def nullsafeneq_sql(self, expression: exp.NullSafeNEQ) -> str:
3769        return self.binary(expression, "IS DISTINCT FROM")
3770
3771    def slice_sql(self, expression: exp.Slice) -> str:
3772        return self.binary(expression, ":")
3773
3774    def sub_sql(self, expression: exp.Sub) -> str:
3775        return self.binary(expression, "-")
3776
3777    def trycast_sql(self, expression: exp.TryCast) -> str:
3778        return self.cast_sql(expression, safe_prefix="TRY_")
3779
3780    def jsoncast_sql(self, expression: exp.JSONCast) -> str:
3781        return self.cast_sql(expression)
3782
3783    def try_sql(self, expression: exp.Try) -> str:
3784        if not self.TRY_SUPPORTED:
3785            self.unsupported("Unsupported TRY function")
3786            return self.sql(expression, "this")
3787
3788        return self.func("TRY", expression.this)
3789
3790    def log_sql(self, expression: exp.Log) -> str:
3791        this = expression.this
3792        expr = expression.expression
3793
3794        if self.dialect.LOG_BASE_FIRST is False:
3795            this, expr = expr, this
3796        elif self.dialect.LOG_BASE_FIRST is None and expr:
3797            if this.name in ("2", "10"):
3798                return self.func(f"LOG{this.name}", expr)
3799
3800            self.unsupported(f"Unsupported logarithm with base {self.sql(this)}")
3801
3802        return self.func("LOG", this, expr)
3803
3804    def use_sql(self, expression: exp.Use) -> str:
3805        kind = self.sql(expression, "kind")
3806        kind = f" {kind}" if kind else ""
3807        this = self.sql(expression, "this") or self.expressions(expression, flat=True)
3808        this = f" {this}" if this else ""
3809        return f"USE{kind}{this}"
3810
3811    def binary(self, expression: exp.Binary, op: str) -> str:
3812        sqls: t.List[str] = []
3813        stack: t.List[t.Union[str, exp.Expression]] = [expression]
3814        binary_type = type(expression)
3815
3816        while stack:
3817            node = stack.pop()
3818
3819            if type(node) is binary_type:
3820                op_func = node.args.get("operator")
3821                if op_func:
3822                    op = f"OPERATOR({self.sql(op_func)})"
3823
3824                stack.append(node.right)
3825                stack.append(f" {self.maybe_comment(op, comments=node.comments)} ")
3826                stack.append(node.left)
3827            else:
3828                sqls.append(self.sql(node))
3829
3830        return "".join(sqls)
3831
3832    def ceil_floor(self, expression: exp.Ceil | exp.Floor) -> str:
3833        to_clause = self.sql(expression, "to")
3834        if to_clause:
3835            return f"{expression.sql_name()}({self.sql(expression, 'this')} TO {to_clause})"
3836
3837        return self.function_fallback_sql(expression)
3838
3839    def function_fallback_sql(self, expression: exp.Func) -> str:
3840        args = []
3841
3842        for key in expression.arg_types:
3843            arg_value = expression.args.get(key)
3844
3845            if isinstance(arg_value, list):
3846                for value in arg_value:
3847                    args.append(value)
3848            elif arg_value is not None:
3849                args.append(arg_value)
3850
3851        if self.dialect.PRESERVE_ORIGINAL_NAMES:
3852            name = (expression._meta and expression.meta.get("name")) or expression.sql_name()
3853        else:
3854            name = expression.sql_name()
3855
3856        return self.func(name, *args)
3857
3858    def func(
3859        self,
3860        name: str,
3861        *args: t.Optional[exp.Expression | str],
3862        prefix: str = "(",
3863        suffix: str = ")",
3864        normalize: bool = True,
3865    ) -> str:
3866        name = self.normalize_func(name) if normalize else name
3867        return f"{name}{prefix}{self.format_args(*args)}{suffix}"
3868
3869    def format_args(self, *args: t.Optional[str | exp.Expression], sep: str = ", ") -> str:
3870        arg_sqls = tuple(
3871            self.sql(arg) for arg in args if arg is not None and not isinstance(arg, bool)
3872        )
3873        if self.pretty and self.too_wide(arg_sqls):
3874            return self.indent(
3875                "\n" + f"{sep.strip()}\n".join(arg_sqls) + "\n", skip_first=True, skip_last=True
3876            )
3877        return sep.join(arg_sqls)
3878
3879    def too_wide(self, args: t.Iterable) -> bool:
3880        return sum(len(arg) for arg in args) > self.max_text_width
3881
3882    def format_time(
3883        self,
3884        expression: exp.Expression,
3885        inverse_time_mapping: t.Optional[t.Dict[str, str]] = None,
3886        inverse_time_trie: t.Optional[t.Dict] = None,
3887    ) -> t.Optional[str]:
3888        return format_time(
3889            self.sql(expression, "format"),
3890            inverse_time_mapping or self.dialect.INVERSE_TIME_MAPPING,
3891            inverse_time_trie or self.dialect.INVERSE_TIME_TRIE,
3892        )
3893
3894    def expressions(
3895        self,
3896        expression: t.Optional[exp.Expression] = None,
3897        key: t.Optional[str] = None,
3898        sqls: t.Optional[t.Collection[str | exp.Expression]] = None,
3899        flat: bool = False,
3900        indent: bool = True,
3901        skip_first: bool = False,
3902        skip_last: bool = False,
3903        sep: str = ", ",
3904        prefix: str = "",
3905        dynamic: bool = False,
3906        new_line: bool = False,
3907    ) -> str:
3908        expressions = expression.args.get(key or "expressions") if expression else sqls
3909
3910        if not expressions:
3911            return ""
3912
3913        if flat:
3914            return sep.join(sql for sql in (self.sql(e) for e in expressions) if sql)
3915
3916        num_sqls = len(expressions)
3917        result_sqls = []
3918
3919        for i, e in enumerate(expressions):
3920            sql = self.sql(e, comment=False)
3921            if not sql:
3922                continue
3923
3924            comments = self.maybe_comment("", e) if isinstance(e, exp.Expression) else ""
3925
3926            if self.pretty:
3927                if self.leading_comma:
3928                    result_sqls.append(f"{sep if i > 0 else ''}{prefix}{sql}{comments}")
3929                else:
3930                    result_sqls.append(
3931                        f"{prefix}{sql}{(sep.rstrip() if comments else sep) if i + 1 < num_sqls else ''}{comments}"
3932                    )
3933            else:
3934                result_sqls.append(f"{prefix}{sql}{comments}{sep if i + 1 < num_sqls else ''}")
3935
3936        if self.pretty and (not dynamic or self.too_wide(result_sqls)):
3937            if new_line:
3938                result_sqls.insert(0, "")
3939                result_sqls.append("")
3940            result_sql = "\n".join(s.rstrip() for s in result_sqls)
3941        else:
3942            result_sql = "".join(result_sqls)
3943
3944        return (
3945            self.indent(result_sql, skip_first=skip_first, skip_last=skip_last)
3946            if indent
3947            else result_sql
3948        )
3949
3950    def op_expressions(self, op: str, expression: exp.Expression, flat: bool = False) -> str:
3951        flat = flat or isinstance(expression.parent, exp.Properties)
3952        expressions_sql = self.expressions(expression, flat=flat)
3953        if flat:
3954            return f"{op} {expressions_sql}"
3955        return f"{self.seg(op)}{self.sep() if expressions_sql else ''}{expressions_sql}"
3956
3957    def naked_property(self, expression: exp.Property) -> str:
3958        property_name = exp.Properties.PROPERTY_TO_NAME.get(expression.__class__)
3959        if not property_name:
3960            self.unsupported(f"Unsupported property {expression.__class__.__name__}")
3961        return f"{property_name} {self.sql(expression, 'this')}"
3962
3963    def tag_sql(self, expression: exp.Tag) -> str:
3964        return f"{expression.args.get('prefix')}{self.sql(expression.this)}{expression.args.get('postfix')}"
3965
3966    def token_sql(self, token_type: TokenType) -> str:
3967        return self.TOKEN_MAPPING.get(token_type, token_type.name)
3968
3969    def userdefinedfunction_sql(self, expression: exp.UserDefinedFunction) -> str:
3970        this = self.sql(expression, "this")
3971        expressions = self.no_identify(self.expressions, expression)
3972        expressions = (
3973            self.wrap(expressions) if expression.args.get("wrapped") else f" {expressions}"
3974        )
3975        return f"{this}{expressions}" if expressions.strip() != "" else this
3976
3977    def joinhint_sql(self, expression: exp.JoinHint) -> str:
3978        this = self.sql(expression, "this")
3979        expressions = self.expressions(expression, flat=True)
3980        return f"{this}({expressions})"
3981
3982    def kwarg_sql(self, expression: exp.Kwarg) -> str:
3983        return self.binary(expression, "=>")
3984
3985    def when_sql(self, expression: exp.When) -> str:
3986        matched = "MATCHED" if expression.args["matched"] else "NOT MATCHED"
3987        source = " BY SOURCE" if self.MATCHED_BY_SOURCE and expression.args.get("source") else ""
3988        condition = self.sql(expression, "condition")
3989        condition = f" AND {condition}" if condition else ""
3990
3991        then_expression = expression.args.get("then")
3992        if isinstance(then_expression, exp.Insert):
3993            this = self.sql(then_expression, "this")
3994            this = f"INSERT {this}" if this else "INSERT"
3995            then = self.sql(then_expression, "expression")
3996            then = f"{this} VALUES {then}" if then else this
3997        elif isinstance(then_expression, exp.Update):
3998            if isinstance(then_expression.args.get("expressions"), exp.Star):
3999                then = f"UPDATE {self.sql(then_expression, 'expressions')}"
4000            else:
4001                then = f"UPDATE SET{self.sep()}{self.expressions(then_expression)}"
4002        else:
4003            then = self.sql(then_expression)
4004        return f"WHEN {matched}{source}{condition} THEN {then}"
4005
4006    def whens_sql(self, expression: exp.Whens) -> str:
4007        return self.expressions(expression, sep=" ", indent=False)
4008
4009    def merge_sql(self, expression: exp.Merge) -> str:
4010        table = expression.this
4011        table_alias = ""
4012
4013        hints = table.args.get("hints")
4014        if hints and table.alias and isinstance(hints[0], exp.WithTableHint):
4015            # T-SQL syntax is MERGE ... <target_table> [WITH (<merge_hint>)] [[AS] table_alias]
4016            table_alias = f" AS {self.sql(table.args['alias'].pop())}"
4017
4018        this = self.sql(table)
4019        using = f"USING {self.sql(expression, 'using')}"
4020        on = f"ON {self.sql(expression, 'on')}"
4021        whens = self.sql(expression, "whens")
4022
4023        returning = self.sql(expression, "returning")
4024        if returning:
4025            whens = f"{whens}{returning}"
4026
4027        sep = self.sep()
4028
4029        return self.prepend_ctes(
4030            expression,
4031            f"MERGE INTO {this}{table_alias}{sep}{using}{sep}{on}{sep}{whens}",
4032        )
4033
4034    @unsupported_args("format")
4035    def tochar_sql(self, expression: exp.ToChar) -> str:
4036        return self.sql(exp.cast(expression.this, exp.DataType.Type.TEXT))
4037
4038    def tonumber_sql(self, expression: exp.ToNumber) -> str:
4039        if not self.SUPPORTS_TO_NUMBER:
4040            self.unsupported("Unsupported TO_NUMBER function")
4041            return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
4042
4043        fmt = expression.args.get("format")
4044        if not fmt:
4045            self.unsupported("Conversion format is required for TO_NUMBER")
4046            return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
4047
4048        return self.func("TO_NUMBER", expression.this, fmt)
4049
4050    def dictproperty_sql(self, expression: exp.DictProperty) -> str:
4051        this = self.sql(expression, "this")
4052        kind = self.sql(expression, "kind")
4053        settings_sql = self.expressions(expression, key="settings", sep=" ")
4054        args = f"({self.sep('')}{settings_sql}{self.seg(')', sep='')}" if settings_sql else "()"
4055        return f"{this}({kind}{args})"
4056
4057    def dictrange_sql(self, expression: exp.DictRange) -> str:
4058        this = self.sql(expression, "this")
4059        max = self.sql(expression, "max")
4060        min = self.sql(expression, "min")
4061        return f"{this}(MIN {min} MAX {max})"
4062
4063    def dictsubproperty_sql(self, expression: exp.DictSubProperty) -> str:
4064        return f"{self.sql(expression, 'this')} {self.sql(expression, 'value')}"
4065
4066    def duplicatekeyproperty_sql(self, expression: exp.DuplicateKeyProperty) -> str:
4067        return f"DUPLICATE KEY ({self.expressions(expression, flat=True)})"
4068
4069    # https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE/
4070    def uniquekeyproperty_sql(
4071        self, expression: exp.UniqueKeyProperty, prefix: str = "UNIQUE KEY"
4072    ) -> str:
4073        return f"{prefix} ({self.expressions(expression, flat=True)})"
4074
4075    # https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
4076    def distributedbyproperty_sql(self, expression: exp.DistributedByProperty) -> str:
4077        expressions = self.expressions(expression, flat=True)
4078        expressions = f" {self.wrap(expressions)}" if expressions else ""
4079        buckets = self.sql(expression, "buckets")
4080        kind = self.sql(expression, "kind")
4081        buckets = f" BUCKETS {buckets}" if buckets else ""
4082        order = self.sql(expression, "order")
4083        return f"DISTRIBUTED BY {kind}{expressions}{buckets}{order}"
4084
4085    def oncluster_sql(self, expression: exp.OnCluster) -> str:
4086        return ""
4087
4088    def clusteredbyproperty_sql(self, expression: exp.ClusteredByProperty) -> str:
4089        expressions = self.expressions(expression, key="expressions", flat=True)
4090        sorted_by = self.expressions(expression, key="sorted_by", flat=True)
4091        sorted_by = f" SORTED BY ({sorted_by})" if sorted_by else ""
4092        buckets = self.sql(expression, "buckets")
4093        return f"CLUSTERED BY ({expressions}){sorted_by} INTO {buckets} BUCKETS"
4094
4095    def anyvalue_sql(self, expression: exp.AnyValue) -> str:
4096        this = self.sql(expression, "this")
4097        having = self.sql(expression, "having")
4098
4099        if having:
4100            this = f"{this} HAVING {'MAX' if expression.args.get('max') else 'MIN'} {having}"
4101
4102        return self.func("ANY_VALUE", this)
4103
4104    def querytransform_sql(self, expression: exp.QueryTransform) -> str:
4105        transform = self.func("TRANSFORM", *expression.expressions)
4106        row_format_before = self.sql(expression, "row_format_before")
4107        row_format_before = f" {row_format_before}" if row_format_before else ""
4108        record_writer = self.sql(expression, "record_writer")
4109        record_writer = f" RECORDWRITER {record_writer}" if record_writer else ""
4110        using = f" USING {self.sql(expression, 'command_script')}"
4111        schema = self.sql(expression, "schema")
4112        schema = f" AS {schema}" if schema else ""
4113        row_format_after = self.sql(expression, "row_format_after")
4114        row_format_after = f" {row_format_after}" if row_format_after else ""
4115        record_reader = self.sql(expression, "record_reader")
4116        record_reader = f" RECORDREADER {record_reader}" if record_reader else ""
4117        return f"{transform}{row_format_before}{record_writer}{using}{schema}{row_format_after}{record_reader}"
4118
4119    def indexconstraintoption_sql(self, expression: exp.IndexConstraintOption) -> str:
4120        key_block_size = self.sql(expression, "key_block_size")
4121        if key_block_size:
4122            return f"KEY_BLOCK_SIZE = {key_block_size}"
4123
4124        using = self.sql(expression, "using")
4125        if using:
4126            return f"USING {using}"
4127
4128        parser = self.sql(expression, "parser")
4129        if parser:
4130            return f"WITH PARSER {parser}"
4131
4132        comment = self.sql(expression, "comment")
4133        if comment:
4134            return f"COMMENT {comment}"
4135
4136        visible = expression.args.get("visible")
4137        if visible is not None:
4138            return "VISIBLE" if visible else "INVISIBLE"
4139
4140        engine_attr = self.sql(expression, "engine_attr")
4141        if engine_attr:
4142            return f"ENGINE_ATTRIBUTE = {engine_attr}"
4143
4144        secondary_engine_attr = self.sql(expression, "secondary_engine_attr")
4145        if secondary_engine_attr:
4146            return f"SECONDARY_ENGINE_ATTRIBUTE = {secondary_engine_attr}"
4147
4148        self.unsupported("Unsupported index constraint option.")
4149        return ""
4150
4151    def checkcolumnconstraint_sql(self, expression: exp.CheckColumnConstraint) -> str:
4152        enforced = " ENFORCED" if expression.args.get("enforced") else ""
4153        return f"CHECK ({self.sql(expression, 'this')}){enforced}"
4154
4155    def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
4156        kind = self.sql(expression, "kind")
4157        kind = f"{kind} INDEX" if kind else "INDEX"
4158        this = self.sql(expression, "this")
4159        this = f" {this}" if this else ""
4160        index_type = self.sql(expression, "index_type")
4161        index_type = f" USING {index_type}" if index_type else ""
4162        expressions = self.expressions(expression, flat=True)
4163        expressions = f" ({expressions})" if expressions else ""
4164        options = self.expressions(expression, key="options", sep=" ")
4165        options = f" {options}" if options else ""
4166        return f"{kind}{this}{index_type}{expressions}{options}"
4167
4168    def nvl2_sql(self, expression: exp.Nvl2) -> str:
4169        if self.NVL2_SUPPORTED:
4170            return self.function_fallback_sql(expression)
4171
4172        case = exp.Case().when(
4173            expression.this.is_(exp.null()).not_(copy=False),
4174            expression.args["true"],
4175            copy=False,
4176        )
4177        else_cond = expression.args.get("false")
4178        if else_cond:
4179            case.else_(else_cond, copy=False)
4180
4181        return self.sql(case)
4182
4183    def comprehension_sql(self, expression: exp.Comprehension) -> str:
4184        this = self.sql(expression, "this")
4185        expr = self.sql(expression, "expression")
4186        iterator = self.sql(expression, "iterator")
4187        condition = self.sql(expression, "condition")
4188        condition = f" IF {condition}" if condition else ""
4189        return f"{this} FOR {expr} IN {iterator}{condition}"
4190
4191    def columnprefix_sql(self, expression: exp.ColumnPrefix) -> str:
4192        return f"{self.sql(expression, 'this')}({self.sql(expression, 'expression')})"
4193
4194    def opclass_sql(self, expression: exp.Opclass) -> str:
4195        return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
4196
4197    def predict_sql(self, expression: exp.Predict) -> str:
4198        model = self.sql(expression, "this")
4199        model = f"MODEL {model}"
4200        table = self.sql(expression, "expression")
4201        table = f"TABLE {table}" if not isinstance(expression.expression, exp.Subquery) else table
4202        parameters = self.sql(expression, "params_struct")
4203        return self.func("PREDICT", model, table, parameters or None)
4204
4205    def generateembedding_sql(self, expression: exp.GenerateEmbedding) -> str:
4206        model = self.sql(expression, "this")
4207        model = f"MODEL {model}"
4208        table = self.sql(expression, "expression")
4209        table = f"TABLE {table}" if not isinstance(expression.expression, exp.Subquery) else table
4210        parameters = self.sql(expression, "params_struct")
4211        return self.func("GENERATE_EMBEDDING", model, table, parameters or None)
4212
4213    def featuresattime_sql(self, expression: exp.FeaturesAtTime) -> str:
4214        this_sql = self.sql(expression, "this")
4215        if isinstance(expression.this, exp.Table):
4216            this_sql = f"TABLE {this_sql}"
4217
4218        return self.func(
4219            "FEATURES_AT_TIME",
4220            this_sql,
4221            expression.args.get("time"),
4222            expression.args.get("num_rows"),
4223            expression.args.get("ignore_feature_nulls"),
4224        )
4225
4226    def vectorsearch_sql(self, expression: exp.VectorSearch) -> str:
4227        this_sql = self.sql(expression, "this")
4228        if isinstance(expression.this, exp.Table):
4229            this_sql = f"TABLE {this_sql}"
4230
4231        query_table = self.sql(expression, "query_table")
4232        if isinstance(expression.args["query_table"], exp.Table):
4233            query_table = f"TABLE {query_table}"
4234
4235        return self.func(
4236            "VECTOR_SEARCH",
4237            this_sql,
4238            expression.args.get("column_to_search"),
4239            query_table,
4240            expression.args.get("query_column_to_search"),
4241            expression.args.get("top_k"),
4242            expression.args.get("distance_type"),
4243            expression.args.get("options"),
4244        )
4245
4246    def forin_sql(self, expression: exp.ForIn) -> str:
4247        this = self.sql(expression, "this")
4248        expression_sql = self.sql(expression, "expression")
4249        return f"FOR {this} DO {expression_sql}"
4250
4251    def refresh_sql(self, expression: exp.Refresh) -> str:
4252        this = self.sql(expression, "this")
4253        table = "" if isinstance(expression.this, exp.Literal) else "TABLE "
4254        return f"REFRESH {table}{this}"
4255
4256    def toarray_sql(self, expression: exp.ToArray) -> str:
4257        arg = expression.this
4258        if not arg.type:
4259            from sqlglot.optimizer.annotate_types import annotate_types
4260
4261            arg = annotate_types(arg, dialect=self.dialect)
4262
4263        if arg.is_type(exp.DataType.Type.ARRAY):
4264            return self.sql(arg)
4265
4266        cond_for_null = arg.is_(exp.null())
4267        return self.sql(exp.func("IF", cond_for_null, exp.null(), exp.array(arg, copy=False)))
4268
4269    def tsordstotime_sql(self, expression: exp.TsOrDsToTime) -> str:
4270        this = expression.this
4271        time_format = self.format_time(expression)
4272
4273        if time_format:
4274            return self.sql(
4275                exp.cast(
4276                    exp.StrToTime(this=this, format=expression.args["format"]),
4277                    exp.DataType.Type.TIME,
4278                )
4279            )
4280
4281        if isinstance(this, exp.TsOrDsToTime) or this.is_type(exp.DataType.Type.TIME):
4282            return self.sql(this)
4283
4284        return self.sql(exp.cast(this, exp.DataType.Type.TIME))
4285
4286    def tsordstotimestamp_sql(self, expression: exp.TsOrDsToTimestamp) -> str:
4287        this = expression.this
4288        if isinstance(this, exp.TsOrDsToTimestamp) or this.is_type(exp.DataType.Type.TIMESTAMP):
4289            return self.sql(this)
4290
4291        return self.sql(exp.cast(this, exp.DataType.Type.TIMESTAMP, dialect=self.dialect))
4292
4293    def tsordstodatetime_sql(self, expression: exp.TsOrDsToDatetime) -> str:
4294        this = expression.this
4295        if isinstance(this, exp.TsOrDsToDatetime) or this.is_type(exp.DataType.Type.DATETIME):
4296            return self.sql(this)
4297
4298        return self.sql(exp.cast(this, exp.DataType.Type.DATETIME, dialect=self.dialect))
4299
4300    def tsordstodate_sql(self, expression: exp.TsOrDsToDate) -> str:
4301        this = expression.this
4302        time_format = self.format_time(expression)
4303
4304        if time_format and time_format not in (self.dialect.TIME_FORMAT, self.dialect.DATE_FORMAT):
4305            return self.sql(
4306                exp.cast(
4307                    exp.StrToTime(this=this, format=expression.args["format"]),
4308                    exp.DataType.Type.DATE,
4309                )
4310            )
4311
4312        if isinstance(this, exp.TsOrDsToDate) or this.is_type(exp.DataType.Type.DATE):
4313            return self.sql(this)
4314
4315        return self.sql(exp.cast(this, exp.DataType.Type.DATE))
4316
4317    def unixdate_sql(self, expression: exp.UnixDate) -> str:
4318        return self.sql(
4319            exp.func(
4320                "DATEDIFF",
4321                expression.this,
4322                exp.cast(exp.Literal.string("1970-01-01"), exp.DataType.Type.DATE),
4323                "day",
4324            )
4325        )
4326
4327    def lastday_sql(self, expression: exp.LastDay) -> str:
4328        if self.LAST_DAY_SUPPORTS_DATE_PART:
4329            return self.function_fallback_sql(expression)
4330
4331        unit = expression.text("unit")
4332        if unit and unit != "MONTH":
4333            self.unsupported("Date parts are not supported in LAST_DAY.")
4334
4335        return self.func("LAST_DAY", expression.this)
4336
4337    def dateadd_sql(self, expression: exp.DateAdd) -> str:
4338        from sqlglot.dialects.dialect import unit_to_str
4339
4340        return self.func(
4341            "DATE_ADD", expression.this, expression.expression, unit_to_str(expression)
4342        )
4343
4344    def arrayany_sql(self, expression: exp.ArrayAny) -> str:
4345        if self.CAN_IMPLEMENT_ARRAY_ANY:
4346            filtered = exp.ArrayFilter(this=expression.this, expression=expression.expression)
4347            filtered_not_empty = exp.ArraySize(this=filtered).neq(0)
4348            original_is_empty = exp.ArraySize(this=expression.this).eq(0)
4349            return self.sql(exp.paren(original_is_empty.or_(filtered_not_empty)))
4350
4351        from sqlglot.dialects import Dialect
4352
4353        # SQLGlot's executor supports ARRAY_ANY, so we don't wanna warn for the SQLGlot dialect
4354        if self.dialect.__class__ != Dialect:
4355            self.unsupported("ARRAY_ANY is unsupported")
4356
4357        return self.function_fallback_sql(expression)
4358
4359    def struct_sql(self, expression: exp.Struct) -> str:
4360        expression.set(
4361            "expressions",
4362            [
4363                exp.alias_(e.expression, e.name if e.this.is_string else e.this)
4364                if isinstance(e, exp.PropertyEQ)
4365                else e
4366                for e in expression.expressions
4367            ],
4368        )
4369
4370        return self.function_fallback_sql(expression)
4371
4372    def partitionrange_sql(self, expression: exp.PartitionRange) -> str:
4373        low = self.sql(expression, "this")
4374        high = self.sql(expression, "expression")
4375
4376        return f"{low} TO {high}"
4377
4378    def truncatetable_sql(self, expression: exp.TruncateTable) -> str:
4379        target = "DATABASE" if expression.args.get("is_database") else "TABLE"
4380        tables = f" {self.expressions(expression)}"
4381
4382        exists = " IF EXISTS" if expression.args.get("exists") else ""
4383
4384        on_cluster = self.sql(expression, "cluster")
4385        on_cluster = f" {on_cluster}" if on_cluster else ""
4386
4387        identity = self.sql(expression, "identity")
4388        identity = f" {identity} IDENTITY" if identity else ""
4389
4390        option = self.sql(expression, "option")
4391        option = f" {option}" if option else ""
4392
4393        partition = self.sql(expression, "partition")
4394        partition = f" {partition}" if partition else ""
4395
4396        return f"TRUNCATE {target}{exists}{tables}{on_cluster}{identity}{option}{partition}"
4397
4398    # This transpiles T-SQL's CONVERT function
4399    # https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16
4400    def convert_sql(self, expression: exp.Convert) -> str:
4401        to = expression.this
4402        value = expression.expression
4403        style = expression.args.get("style")
4404        safe = expression.args.get("safe")
4405        strict = expression.args.get("strict")
4406
4407        if not to or not value:
4408            return ""
4409
4410        # Retrieve length of datatype and override to default if not specified
4411        if not seq_get(to.expressions, 0) and to.this in self.PARAMETERIZABLE_TEXT_TYPES:
4412            to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False)
4413
4414        transformed: t.Optional[exp.Expression] = None
4415        cast = exp.Cast if strict else exp.TryCast
4416
4417        # Check whether a conversion with format (T-SQL calls this 'style') is applicable
4418        if isinstance(style, exp.Literal) and style.is_int:
4419            from sqlglot.dialects.tsql import TSQL
4420
4421            style_value = style.name
4422            converted_style = TSQL.CONVERT_FORMAT_MAPPING.get(style_value)
4423            if not converted_style:
4424                self.unsupported(f"Unsupported T-SQL 'style' value: {style_value}")
4425
4426            fmt = exp.Literal.string(converted_style)
4427
4428            if to.this == exp.DataType.Type.DATE:
4429                transformed = exp.StrToDate(this=value, format=fmt)
4430            elif to.this in (exp.DataType.Type.DATETIME, exp.DataType.Type.DATETIME2):
4431                transformed = exp.StrToTime(this=value, format=fmt)
4432            elif to.this in self.PARAMETERIZABLE_TEXT_TYPES:
4433                transformed = cast(this=exp.TimeToStr(this=value, format=fmt), to=to, safe=safe)
4434            elif to.this == exp.DataType.Type.TEXT:
4435                transformed = exp.TimeToStr(this=value, format=fmt)
4436
4437        if not transformed:
4438            transformed = cast(this=value, to=to, safe=safe)
4439
4440        return self.sql(transformed)
4441
4442    def _jsonpathkey_sql(self, expression: exp.JSONPathKey) -> str:
4443        this = expression.this
4444        if isinstance(this, exp.JSONPathWildcard):
4445            this = self.json_path_part(this)
4446            return f".{this}" if this else ""
4447
4448        if self.SAFE_JSON_PATH_KEY_RE.match(this):
4449            return f".{this}"
4450
4451        this = self.json_path_part(this)
4452        return (
4453            f"[{this}]"
4454            if self._quote_json_path_key_using_brackets and self.JSON_PATH_BRACKETED_KEY_SUPPORTED
4455            else f".{this}"
4456        )
4457
4458    def _jsonpathsubscript_sql(self, expression: exp.JSONPathSubscript) -> str:
4459        this = self.json_path_part(expression.this)
4460        return f"[{this}]" if this else ""
4461
4462    def _simplify_unless_literal(self, expression: E) -> E:
4463        if not isinstance(expression, exp.Literal):
4464            from sqlglot.optimizer.simplify import simplify
4465
4466            expression = simplify(expression, dialect=self.dialect)
4467
4468        return expression
4469
4470    def _embed_ignore_nulls(self, expression: exp.IgnoreNulls | exp.RespectNulls, text: str) -> str:
4471        this = expression.this
4472        if isinstance(this, self.RESPECT_IGNORE_NULLS_UNSUPPORTED_EXPRESSIONS):
4473            self.unsupported(
4474                f"RESPECT/IGNORE NULLS is not supported for {type(this).key} in {self.dialect.__class__.__name__}"
4475            )
4476            return self.sql(this)
4477
4478        if self.IGNORE_NULLS_IN_FUNC and not expression.meta.get("inline"):
4479            # The first modifier here will be the one closest to the AggFunc's arg
4480            mods = sorted(
4481                expression.find_all(exp.HavingMax, exp.Order, exp.Limit),
4482                key=lambda x: 0
4483                if isinstance(x, exp.HavingMax)
4484                else (1 if isinstance(x, exp.Order) else 2),
4485            )
4486
4487            if mods:
4488                mod = mods[0]
4489                this = expression.__class__(this=mod.this.copy())
4490                this.meta["inline"] = True
4491                mod.this.replace(this)
4492                return self.sql(expression.this)
4493
4494            agg_func = expression.find(exp.AggFunc)
4495
4496            if agg_func:
4497                agg_func_sql = self.sql(agg_func, comment=False)[:-1] + f" {text})"
4498                return self.maybe_comment(agg_func_sql, comments=agg_func.comments)
4499
4500        return f"{self.sql(expression, 'this')} {text}"
4501
4502    def _replace_line_breaks(self, string: str) -> str:
4503        """We don't want to extra indent line breaks so we temporarily replace them with sentinels."""
4504        if self.pretty:
4505            return string.replace("\n", self.SENTINEL_LINE_BREAK)
4506        return string
4507
4508    def copyparameter_sql(self, expression: exp.CopyParameter) -> str:
4509        option = self.sql(expression, "this")
4510
4511        if expression.expressions:
4512            upper = option.upper()
4513
4514            # Snowflake FILE_FORMAT options are separated by whitespace
4515            sep = " " if upper == "FILE_FORMAT" else ", "
4516
4517            # Databricks copy/format options do not set their list of values with EQ
4518            op = " " if upper in ("COPY_OPTIONS", "FORMAT_OPTIONS") else " = "
4519            values = self.expressions(expression, flat=True, sep=sep)
4520            return f"{option}{op}({values})"
4521
4522        value = self.sql(expression, "expression")
4523
4524        if not value:
4525            return option
4526
4527        op = " = " if self.COPY_PARAMS_EQ_REQUIRED else " "
4528
4529        return f"{option}{op}{value}"
4530
4531    def credentials_sql(self, expression: exp.Credentials) -> str:
4532        cred_expr = expression.args.get("credentials")
4533        if isinstance(cred_expr, exp.Literal):
4534            # Redshift case: CREDENTIALS <string>
4535            credentials = self.sql(expression, "credentials")
4536            credentials = f"CREDENTIALS {credentials}" if credentials else ""
4537        else:
4538            # Snowflake case: CREDENTIALS = (...)
4539            credentials = self.expressions(expression, key="credentials", flat=True, sep=" ")
4540            credentials = f"CREDENTIALS = ({credentials})" if cred_expr is not None else ""
4541
4542        storage = self.sql(expression, "storage")
4543        storage = f"STORAGE_INTEGRATION = {storage}" if storage else ""
4544
4545        encryption = self.expressions(expression, key="encryption", flat=True, sep=" ")
4546        encryption = f" ENCRYPTION = ({encryption})" if encryption else ""
4547
4548        iam_role = self.sql(expression, "iam_role")
4549        iam_role = f"IAM_ROLE {iam_role}" if iam_role else ""
4550
4551        region = self.sql(expression, "region")
4552        region = f" REGION {region}" if region else ""
4553
4554        return f"{credentials}{storage}{encryption}{iam_role}{region}"
4555
4556    def copy_sql(self, expression: exp.Copy) -> str:
4557        this = self.sql(expression, "this")
4558        this = f" INTO {this}" if self.COPY_HAS_INTO_KEYWORD else f" {this}"
4559
4560        credentials = self.sql(expression, "credentials")
4561        credentials = self.seg(credentials) if credentials else ""
4562        kind = self.seg("FROM" if expression.args.get("kind") else "TO")
4563        files = self.expressions(expression, key="files", flat=True)
4564
4565        sep = ", " if self.dialect.COPY_PARAMS_ARE_CSV else " "
4566        params = self.expressions(
4567            expression,
4568            key="params",
4569            sep=sep,
4570            new_line=True,
4571            skip_last=True,
4572            skip_first=True,
4573            indent=self.COPY_PARAMS_ARE_WRAPPED,
4574        )
4575
4576        if params:
4577            if self.COPY_PARAMS_ARE_WRAPPED:
4578                params = f" WITH ({params})"
4579            elif not self.pretty:
4580                params = f" {params}"
4581
4582        return f"COPY{this}{kind} {files}{credentials}{params}"
4583
4584    def semicolon_sql(self, expression: exp.Semicolon) -> str:
4585        return ""
4586
4587    def datadeletionproperty_sql(self, expression: exp.DataDeletionProperty) -> str:
4588        on_sql = "ON" if expression.args.get("on") else "OFF"
4589        filter_col: t.Optional[str] = self.sql(expression, "filter_column")
4590        filter_col = f"FILTER_COLUMN={filter_col}" if filter_col else None
4591        retention_period: t.Optional[str] = self.sql(expression, "retention_period")
4592        retention_period = f"RETENTION_PERIOD={retention_period}" if retention_period else None
4593
4594        if filter_col or retention_period:
4595            on_sql = self.func("ON", filter_col, retention_period)
4596
4597        return f"DATA_DELETION={on_sql}"
4598
4599    def maskingpolicycolumnconstraint_sql(
4600        self, expression: exp.MaskingPolicyColumnConstraint
4601    ) -> str:
4602        this = self.sql(expression, "this")
4603        expressions = self.expressions(expression, flat=True)
4604        expressions = f" USING ({expressions})" if expressions else ""
4605        return f"MASKING POLICY {this}{expressions}"
4606
4607    def gapfill_sql(self, expression: exp.GapFill) -> str:
4608        this = self.sql(expression, "this")
4609        this = f"TABLE {this}"
4610        return self.func("GAP_FILL", this, *[v for k, v in expression.args.items() if k != "this"])
4611
4612    def scope_resolution(self, rhs: str, scope_name: str) -> str:
4613        return self.func("SCOPE_RESOLUTION", scope_name or None, rhs)
4614
4615    def scoperesolution_sql(self, expression: exp.ScopeResolution) -> str:
4616        this = self.sql(expression, "this")
4617        expr = expression.expression
4618
4619        if isinstance(expr, exp.Func):
4620            # T-SQL's CLR functions are case sensitive
4621            expr = f"{self.sql(expr, 'this')}({self.format_args(*expr.expressions)})"
4622        else:
4623            expr = self.sql(expression, "expression")
4624
4625        return self.scope_resolution(expr, this)
4626
4627    def parsejson_sql(self, expression: exp.ParseJSON) -> str:
4628        if self.PARSE_JSON_NAME is None:
4629            return self.sql(expression.this)
4630
4631        return self.func(self.PARSE_JSON_NAME, expression.this, expression.expression)
4632
4633    def rand_sql(self, expression: exp.Rand) -> str:
4634        lower = self.sql(expression, "lower")
4635        upper = self.sql(expression, "upper")
4636
4637        if lower and upper:
4638            return f"({upper} - {lower}) * {self.func('RAND', expression.this)} + {lower}"
4639        return self.func("RAND", expression.this)
4640
4641    def changes_sql(self, expression: exp.Changes) -> str:
4642        information = self.sql(expression, "information")
4643        information = f"INFORMATION => {information}"
4644        at_before = self.sql(expression, "at_before")
4645        at_before = f"{self.seg('')}{at_before}" if at_before else ""
4646        end = self.sql(expression, "end")
4647        end = f"{self.seg('')}{end}" if end else ""
4648
4649        return f"CHANGES ({information}){at_before}{end}"
4650
4651    def pad_sql(self, expression: exp.Pad) -> str:
4652        prefix = "L" if expression.args.get("is_left") else "R"
4653
4654        fill_pattern = self.sql(expression, "fill_pattern") or None
4655        if not fill_pattern and self.PAD_FILL_PATTERN_IS_REQUIRED:
4656            fill_pattern = "' '"
4657
4658        return self.func(f"{prefix}PAD", expression.this, expression.expression, fill_pattern)
4659
4660    def summarize_sql(self, expression: exp.Summarize) -> str:
4661        table = " TABLE" if expression.args.get("table") else ""
4662        return f"SUMMARIZE{table} {self.sql(expression.this)}"
4663
4664    def explodinggenerateseries_sql(self, expression: exp.ExplodingGenerateSeries) -> str:
4665        generate_series = exp.GenerateSeries(**expression.args)
4666
4667        parent = expression.parent
4668        if isinstance(parent, (exp.Alias, exp.TableAlias)):
4669            parent = parent.parent
4670
4671        if self.SUPPORTS_EXPLODING_PROJECTIONS and not isinstance(parent, (exp.Table, exp.Unnest)):
4672            return self.sql(exp.Unnest(expressions=[generate_series]))
4673
4674        if isinstance(parent, exp.Select):
4675            self.unsupported("GenerateSeries projection unnesting is not supported.")
4676
4677        return self.sql(generate_series)
4678
4679    def arrayconcat_sql(self, expression: exp.ArrayConcat, name: str = "ARRAY_CONCAT") -> str:
4680        exprs = expression.expressions
4681        if not self.ARRAY_CONCAT_IS_VAR_LEN:
4682            if len(exprs) == 0:
4683                rhs: t.Union[str, exp.Expression] = exp.Array(expressions=[])
4684            else:
4685                rhs = reduce(lambda x, y: exp.ArrayConcat(this=x, expressions=[y]), exprs)
4686        else:
4687            rhs = self.expressions(expression)  # type: ignore
4688
4689        return self.func(name, expression.this, rhs or None)
4690
4691    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
4692        if self.SUPPORTS_CONVERT_TIMEZONE:
4693            return self.function_fallback_sql(expression)
4694
4695        source_tz = expression.args.get("source_tz")
4696        target_tz = expression.args.get("target_tz")
4697        timestamp = expression.args.get("timestamp")
4698
4699        if source_tz and timestamp:
4700            timestamp = exp.AtTimeZone(
4701                this=exp.cast(timestamp, exp.DataType.Type.TIMESTAMPNTZ), zone=source_tz
4702            )
4703
4704        expr = exp.AtTimeZone(this=timestamp, zone=target_tz)
4705
4706        return self.sql(expr)
4707
4708    def json_sql(self, expression: exp.JSON) -> str:
4709        this = self.sql(expression, "this")
4710        this = f" {this}" if this else ""
4711
4712        _with = expression.args.get("with")
4713
4714        if _with is None:
4715            with_sql = ""
4716        elif not _with:
4717            with_sql = " WITHOUT"
4718        else:
4719            with_sql = " WITH"
4720
4721        unique_sql = " UNIQUE KEYS" if expression.args.get("unique") else ""
4722
4723        return f"JSON{this}{with_sql}{unique_sql}"
4724
4725    def jsonvalue_sql(self, expression: exp.JSONValue) -> str:
4726        def _generate_on_options(arg: t.Any) -> str:
4727            return arg if isinstance(arg, str) else f"DEFAULT {self.sql(arg)}"
4728
4729        path = self.sql(expression, "path")
4730        returning = self.sql(expression, "returning")
4731        returning = f" RETURNING {returning}" if returning else ""
4732
4733        on_condition = self.sql(expression, "on_condition")
4734        on_condition = f" {on_condition}" if on_condition else ""
4735
4736        return self.func("JSON_VALUE", expression.this, f"{path}{returning}{on_condition}")
4737
4738    def conditionalinsert_sql(self, expression: exp.ConditionalInsert) -> str:
4739        else_ = "ELSE " if expression.args.get("else_") else ""
4740        condition = self.sql(expression, "expression")
4741        condition = f"WHEN {condition} THEN " if condition else else_
4742        insert = self.sql(expression, "this")[len("INSERT") :].strip()
4743        return f"{condition}{insert}"
4744
4745    def multitableinserts_sql(self, expression: exp.MultitableInserts) -> str:
4746        kind = self.sql(expression, "kind")
4747        expressions = self.seg(self.expressions(expression, sep=" "))
4748        res = f"INSERT {kind}{expressions}{self.seg(self.sql(expression, 'source'))}"
4749        return res
4750
4751    def oncondition_sql(self, expression: exp.OnCondition) -> str:
4752        # Static options like "NULL ON ERROR" are stored as strings, in contrast to "DEFAULT <expr> ON ERROR"
4753        empty = expression.args.get("empty")
4754        empty = (
4755            f"DEFAULT {empty} ON EMPTY"
4756            if isinstance(empty, exp.Expression)
4757            else self.sql(expression, "empty")
4758        )
4759
4760        error = expression.args.get("error")
4761        error = (
4762            f"DEFAULT {error} ON ERROR"
4763            if isinstance(error, exp.Expression)
4764            else self.sql(expression, "error")
4765        )
4766
4767        if error and empty:
4768            error = (
4769                f"{empty} {error}"
4770                if self.dialect.ON_CONDITION_EMPTY_BEFORE_ERROR
4771                else f"{error} {empty}"
4772            )
4773            empty = ""
4774
4775        null = self.sql(expression, "null")
4776
4777        return f"{empty}{error}{null}"
4778
4779    def jsonextractquote_sql(self, expression: exp.JSONExtractQuote) -> str:
4780        scalar = " ON SCALAR STRING" if expression.args.get("scalar") else ""
4781        return f"{self.sql(expression, 'option')} QUOTES{scalar}"
4782
4783    def jsonexists_sql(self, expression: exp.JSONExists) -> str:
4784        this = self.sql(expression, "this")
4785        path = self.sql(expression, "path")
4786
4787        passing = self.expressions(expression, "passing")
4788        passing = f" PASSING {passing}" if passing else ""
4789
4790        on_condition = self.sql(expression, "on_condition")
4791        on_condition = f" {on_condition}" if on_condition else ""
4792
4793        path = f"{path}{passing}{on_condition}"
4794
4795        return self.func("JSON_EXISTS", this, path)
4796
4797    def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
4798        array_agg = self.function_fallback_sql(expression)
4799
4800        # Add a NULL FILTER on the column to mimic the results going from a dialect that excludes nulls
4801        # on ARRAY_AGG (e.g Spark) to one that doesn't (e.g. DuckDB)
4802        if self.dialect.ARRAY_AGG_INCLUDES_NULLS and expression.args.get("nulls_excluded"):
4803            parent = expression.parent
4804            if isinstance(parent, exp.Filter):
4805                parent_cond = parent.expression.this
4806                parent_cond.replace(parent_cond.and_(expression.this.is_(exp.null()).not_()))
4807            else:
4808                this = expression.this
4809                # Do not add the filter if the input is not a column (e.g. literal, struct etc)
4810                if this.find(exp.Column):
4811                    # DISTINCT is already present in the agg function, do not propagate it to FILTER as well
4812                    this_sql = (
4813                        self.expressions(this)
4814                        if isinstance(this, exp.Distinct)
4815                        else self.sql(expression, "this")
4816                    )
4817
4818                    array_agg = f"{array_agg} FILTER(WHERE {this_sql} IS NOT NULL)"
4819
4820        return array_agg
4821
4822    def apply_sql(self, expression: exp.Apply) -> str:
4823        this = self.sql(expression, "this")
4824        expr = self.sql(expression, "expression")
4825
4826        return f"{this} APPLY({expr})"
4827
4828    def _grant_or_revoke_sql(
4829        self,
4830        expression: exp.Grant | exp.Revoke,
4831        keyword: str,
4832        preposition: str,
4833        grant_option_prefix: str = "",
4834        grant_option_suffix: str = "",
4835    ) -> str:
4836        privileges_sql = self.expressions(expression, key="privileges", flat=True)
4837
4838        kind = self.sql(expression, "kind")
4839        kind = f" {kind}" if kind else ""
4840
4841        securable = self.sql(expression, "securable")
4842        securable = f" {securable}" if securable else ""
4843
4844        principals = self.expressions(expression, key="principals", flat=True)
4845
4846        if not expression.args.get("grant_option"):
4847            grant_option_prefix = grant_option_suffix = ""
4848
4849        # cascade for revoke only
4850        cascade = self.sql(expression, "cascade")
4851        cascade = f" {cascade}" if cascade else ""
4852
4853        return f"{keyword} {grant_option_prefix}{privileges_sql} ON{kind}{securable} {preposition} {principals}{grant_option_suffix}{cascade}"
4854
4855    def grant_sql(self, expression: exp.Grant) -> str:
4856        return self._grant_or_revoke_sql(
4857            expression,
4858            keyword="GRANT",
4859            preposition="TO",
4860            grant_option_suffix=" WITH GRANT OPTION",
4861        )
4862
4863    def revoke_sql(self, expression: exp.Revoke) -> str:
4864        return self._grant_or_revoke_sql(
4865            expression,
4866            keyword="REVOKE",
4867            preposition="FROM",
4868            grant_option_prefix="GRANT OPTION FOR ",
4869        )
4870
4871    def grantprivilege_sql(self, expression: exp.GrantPrivilege):
4872        this = self.sql(expression, "this")
4873        columns = self.expressions(expression, flat=True)
4874        columns = f"({columns})" if columns else ""
4875
4876        return f"{this}{columns}"
4877
4878    def grantprincipal_sql(self, expression: exp.GrantPrincipal):
4879        this = self.sql(expression, "this")
4880
4881        kind = self.sql(expression, "kind")
4882        kind = f"{kind} " if kind else ""
4883
4884        return f"{kind}{this}"
4885
4886    def columns_sql(self, expression: exp.Columns):
4887        func = self.function_fallback_sql(expression)
4888        if expression.args.get("unpack"):
4889            func = f"*{func}"
4890
4891        return func
4892
4893    def overlay_sql(self, expression: exp.Overlay):
4894        this = self.sql(expression, "this")
4895        expr = self.sql(expression, "expression")
4896        from_sql = self.sql(expression, "from")
4897        for_sql = self.sql(expression, "for")
4898        for_sql = f" FOR {for_sql}" if for_sql else ""
4899
4900        return f"OVERLAY({this} PLACING {expr} FROM {from_sql}{for_sql})"
4901
4902    @unsupported_args("format")
4903    def todouble_sql(self, expression: exp.ToDouble) -> str:
4904        return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
4905
4906    def string_sql(self, expression: exp.String) -> str:
4907        this = expression.this
4908        zone = expression.args.get("zone")
4909
4910        if zone:
4911            # This is a BigQuery specific argument for STRING(<timestamp_expr>, <time_zone>)
4912            # BigQuery stores timestamps internally as UTC, so ConvertTimezone is used with UTC
4913            # set for source_tz to transpile the time conversion before the STRING cast
4914            this = exp.ConvertTimezone(
4915                source_tz=exp.Literal.string("UTC"), target_tz=zone, timestamp=this
4916            )
4917
4918        return self.sql(exp.cast(this, exp.DataType.Type.VARCHAR))
4919
4920    def median_sql(self, expression: exp.Median):
4921        if not self.SUPPORTS_MEDIAN:
4922            return self.sql(
4923                exp.PercentileCont(this=expression.this, expression=exp.Literal.number(0.5))
4924            )
4925
4926        return self.function_fallback_sql(expression)
4927
4928    def overflowtruncatebehavior_sql(self, expression: exp.OverflowTruncateBehavior) -> str:
4929        filler = self.sql(expression, "this")
4930        filler = f" {filler}" if filler else ""
4931        with_count = "WITH COUNT" if expression.args.get("with_count") else "WITHOUT COUNT"
4932        return f"TRUNCATE{filler} {with_count}"
4933
4934    def unixseconds_sql(self, expression: exp.UnixSeconds) -> str:
4935        if self.SUPPORTS_UNIX_SECONDS:
4936            return self.function_fallback_sql(expression)
4937
4938        start_ts = exp.cast(
4939            exp.Literal.string("1970-01-01 00:00:00+00"), to=exp.DataType.Type.TIMESTAMPTZ
4940        )
4941
4942        return self.sql(
4943            exp.TimestampDiff(this=expression.this, expression=start_ts, unit=exp.var("SECONDS"))
4944        )
4945
4946    def arraysize_sql(self, expression: exp.ArraySize) -> str:
4947        dim = expression.expression
4948
4949        # For dialects that don't support the dimension arg, we can safely transpile it's default value (1st dimension)
4950        if dim and self.ARRAY_SIZE_DIM_REQUIRED is None:
4951            if not (dim.is_int and dim.name == "1"):
4952                self.unsupported("Cannot transpile dimension argument for ARRAY_LENGTH")
4953            dim = None
4954
4955        # If dimension is required but not specified, default initialize it
4956        if self.ARRAY_SIZE_DIM_REQUIRED and not dim:
4957            dim = exp.Literal.number(1)
4958
4959        return self.func(self.ARRAY_SIZE_NAME, expression.this, dim)
4960
4961    def attach_sql(self, expression: exp.Attach) -> str:
4962        this = self.sql(expression, "this")
4963        exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else ""
4964        expressions = self.expressions(expression)
4965        expressions = f" ({expressions})" if expressions else ""
4966
4967        return f"ATTACH{exists_sql} {this}{expressions}"
4968
4969    def detach_sql(self, expression: exp.Detach) -> str:
4970        this = self.sql(expression, "this")
4971        # the DATABASE keyword is required if IF EXISTS is set
4972        # without it, DuckDB throws an error: Parser Error: syntax error at or near "exists" (Line Number: 1)
4973        # ref: https://duckdb.org/docs/stable/sql/statements/attach.html#detach-syntax
4974        exists_sql = " DATABASE IF EXISTS" if expression.args.get("exists") else ""
4975
4976        return f"DETACH{exists_sql} {this}"
4977
4978    def attachoption_sql(self, expression: exp.AttachOption) -> str:
4979        this = self.sql(expression, "this")
4980        value = self.sql(expression, "expression")
4981        value = f" {value}" if value else ""
4982        return f"{this}{value}"
4983
4984    def watermarkcolumnconstraint_sql(self, expression: exp.WatermarkColumnConstraint) -> str:
4985        return (
4986            f"WATERMARK FOR {self.sql(expression, 'this')} AS {self.sql(expression, 'expression')}"
4987        )
4988
4989    def encodeproperty_sql(self, expression: exp.EncodeProperty) -> str:
4990        encode = "KEY ENCODE" if expression.args.get("key") else "ENCODE"
4991        encode = f"{encode} {self.sql(expression, 'this')}"
4992
4993        properties = expression.args.get("properties")
4994        if properties:
4995            encode = f"{encode} {self.properties(properties)}"
4996
4997        return encode
4998
4999    def includeproperty_sql(self, expression: exp.IncludeProperty) -> str:
5000        this = self.sql(expression, "this")
5001        include = f"INCLUDE {this}"
5002
5003        column_def = self.sql(expression, "column_def")
5004        if column_def:
5005            include = f"{include} {column_def}"
5006
5007        alias = self.sql(expression, "alias")
5008        if alias:
5009            include = f"{include} AS {alias}"
5010
5011        return include
5012
5013    def xmlelement_sql(self, expression: exp.XMLElement) -> str:
5014        name = f"NAME {self.sql(expression, 'this')}"
5015        return self.func("XMLELEMENT", name, *expression.expressions)
5016
5017    def xmlkeyvalueoption_sql(self, expression: exp.XMLKeyValueOption) -> str:
5018        this = self.sql(expression, "this")
5019        expr = self.sql(expression, "expression")
5020        expr = f"({expr})" if expr else ""
5021        return f"{this}{expr}"
5022
5023    def partitionbyrangeproperty_sql(self, expression: exp.PartitionByRangeProperty) -> str:
5024        partitions = self.expressions(expression, "partition_expressions")
5025        create = self.expressions(expression, "create_expressions")
5026        return f"PARTITION BY RANGE {self.wrap(partitions)} {self.wrap(create)}"
5027
5028    def partitionbyrangepropertydynamic_sql(
5029        self, expression: exp.PartitionByRangePropertyDynamic
5030    ) -> str:
5031        start = self.sql(expression, "start")
5032        end = self.sql(expression, "end")
5033
5034        every = expression.args["every"]
5035        if isinstance(every, exp.Interval) and every.this.is_string:
5036            every.this.replace(exp.Literal.number(every.name))
5037
5038        return f"START {self.wrap(start)} END {self.wrap(end)} EVERY {self.wrap(self.sql(every))}"
5039
5040    def unpivotcolumns_sql(self, expression: exp.UnpivotColumns) -> str:
5041        name = self.sql(expression, "this")
5042        values = self.expressions(expression, flat=True)
5043
5044        return f"NAME {name} VALUE {values}"
5045
5046    def analyzesample_sql(self, expression: exp.AnalyzeSample) -> str:
5047        kind = self.sql(expression, "kind")
5048        sample = self.sql(expression, "sample")
5049        return f"SAMPLE {sample} {kind}"
5050
5051    def analyzestatistics_sql(self, expression: exp.AnalyzeStatistics) -> str:
5052        kind = self.sql(expression, "kind")
5053        option = self.sql(expression, "option")
5054        option = f" {option}" if option else ""
5055        this = self.sql(expression, "this")
5056        this = f" {this}" if this else ""
5057        columns = self.expressions(expression)
5058        columns = f" {columns}" if columns else ""
5059        return f"{kind}{option} STATISTICS{this}{columns}"
5060
5061    def analyzehistogram_sql(self, expression: exp.AnalyzeHistogram) -> str:
5062        this = self.sql(expression, "this")
5063        columns = self.expressions(expression)
5064        inner_expression = self.sql(expression, "expression")
5065        inner_expression = f" {inner_expression}" if inner_expression else ""
5066        update_options = self.sql(expression, "update_options")
5067        update_options = f" {update_options} UPDATE" if update_options else ""
5068        return f"{this} HISTOGRAM ON {columns}{inner_expression}{update_options}"
5069
5070    def analyzedelete_sql(self, expression: exp.AnalyzeDelete) -> str:
5071        kind = self.sql(expression, "kind")
5072        kind = f" {kind}" if kind else ""
5073        return f"DELETE{kind} STATISTICS"
5074
5075    def analyzelistchainedrows_sql(self, expression: exp.AnalyzeListChainedRows) -> str:
5076        inner_expression = self.sql(expression, "expression")
5077        return f"LIST CHAINED ROWS{inner_expression}"
5078
5079    def analyzevalidate_sql(self, expression: exp.AnalyzeValidate) -> str:
5080        kind = self.sql(expression, "kind")
5081        this = self.sql(expression, "this")
5082        this = f" {this}" if this else ""
5083        inner_expression = self.sql(expression, "expression")
5084        return f"VALIDATE {kind}{this}{inner_expression}"
5085
5086    def analyze_sql(self, expression: exp.Analyze) -> str:
5087        options = self.expressions(expression, key="options", sep=" ")
5088        options = f" {options}" if options else ""
5089        kind = self.sql(expression, "kind")
5090        kind = f" {kind}" if kind else ""
5091        this = self.sql(expression, "this")
5092        this = f" {this}" if this else ""
5093        mode = self.sql(expression, "mode")
5094        mode = f" {mode}" if mode else ""
5095        properties = self.sql(expression, "properties")
5096        properties = f" {properties}" if properties else ""
5097        partition = self.sql(expression, "partition")
5098        partition = f" {partition}" if partition else ""
5099        inner_expression = self.sql(expression, "expression")
5100        inner_expression = f" {inner_expression}" if inner_expression else ""
5101        return f"ANALYZE{options}{kind}{this}{partition}{mode}{inner_expression}{properties}"
5102
5103    def xmltable_sql(self, expression: exp.XMLTable) -> str:
5104        this = self.sql(expression, "this")
5105        namespaces = self.expressions(expression, key="namespaces")
5106        namespaces = f"XMLNAMESPACES({namespaces}), " if namespaces else ""
5107        passing = self.expressions(expression, key="passing")
5108        passing = f"{self.sep()}PASSING{self.seg(passing)}" if passing else ""
5109        columns = self.expressions(expression, key="columns")
5110        columns = f"{self.sep()}COLUMNS{self.seg(columns)}" if columns else ""
5111        by_ref = f"{self.sep()}RETURNING SEQUENCE BY REF" if expression.args.get("by_ref") else ""
5112        return f"XMLTABLE({self.sep('')}{self.indent(namespaces + this + passing + by_ref + columns)}{self.seg(')', sep='')}"
5113
5114    def xmlnamespace_sql(self, expression: exp.XMLNamespace) -> str:
5115        this = self.sql(expression, "this")
5116        return this if isinstance(expression.this, exp.Alias) else f"DEFAULT {this}"
5117
5118    def export_sql(self, expression: exp.Export) -> str:
5119        this = self.sql(expression, "this")
5120        connection = self.sql(expression, "connection")
5121        connection = f"WITH CONNECTION {connection} " if connection else ""
5122        options = self.sql(expression, "options")
5123        return f"EXPORT DATA {connection}{options} AS {this}"
5124
5125    def declare_sql(self, expression: exp.Declare) -> str:
5126        return f"DECLARE {self.expressions(expression, flat=True)}"
5127
5128    def declareitem_sql(self, expression: exp.DeclareItem) -> str:
5129        variable = self.sql(expression, "this")
5130        default = self.sql(expression, "default")
5131        default = f" = {default}" if default else ""
5132
5133        kind = self.sql(expression, "kind")
5134        if isinstance(expression.args.get("kind"), exp.Schema):
5135            kind = f"TABLE {kind}"
5136
5137        return f"{variable} AS {kind}{default}"
5138
5139    def recursivewithsearch_sql(self, expression: exp.RecursiveWithSearch) -> str:
5140        kind = self.sql(expression, "kind")
5141        this = self.sql(expression, "this")
5142        set = self.sql(expression, "expression")
5143        using = self.sql(expression, "using")
5144        using = f" USING {using}" if using else ""
5145
5146        kind_sql = kind if kind == "CYCLE" else f"SEARCH {kind} FIRST BY"
5147
5148        return f"{kind_sql} {this} SET {set}{using}"
5149
5150    def parameterizedagg_sql(self, expression: exp.ParameterizedAgg) -> str:
5151        params = self.expressions(expression, key="params", flat=True)
5152        return self.func(expression.name, *expression.expressions) + f"({params})"
5153
5154    def anonymousaggfunc_sql(self, expression: exp.AnonymousAggFunc) -> str:
5155        return self.func(expression.name, *expression.expressions)
5156
5157    def combinedaggfunc_sql(self, expression: exp.CombinedAggFunc) -> str:
5158        return self.anonymousaggfunc_sql(expression)
5159
5160    def combinedparameterizedagg_sql(self, expression: exp.CombinedParameterizedAgg) -> str:
5161        return self.parameterizedagg_sql(expression)
5162
5163    def show_sql(self, expression: exp.Show) -> str:
5164        self.unsupported("Unsupported SHOW statement")
5165        return ""
5166
5167    def get_put_sql(self, expression: exp.Put | exp.Get) -> str:
5168        # Snowflake GET/PUT statements:
5169        #   PUT <file> <internalStage> <properties>
5170        #   GET <internalStage> <file> <properties>
5171        props = expression.args.get("properties")
5172        props_sql = self.properties(props, prefix=" ", sep=" ", wrapped=False) if props else ""
5173        this = self.sql(expression, "this")
5174        target = self.sql(expression, "target")
5175
5176        if isinstance(expression, exp.Put):
5177            return f"PUT {this} {target}{props_sql}"
5178        else:
5179            return f"GET {target} {this}{props_sql}"
5180
5181    def translatecharacters_sql(self, expression: exp.TranslateCharacters):
5182        this = self.sql(expression, "this")
5183        expr = self.sql(expression, "expression")
5184        with_error = " WITH ERROR" if expression.args.get("with_error") else ""
5185        return f"TRANSLATE({this} USING {expr}{with_error})"
5186
5187    def decodecase_sql(self, expression: exp.DecodeCase) -> str:
5188        if self.SUPPORTS_DECODE_CASE:
5189            return self.func("DECODE", *expression.expressions)
5190
5191        expression, *expressions = expression.expressions
5192
5193        ifs = []
5194        for search, result in zip(expressions[::2], expressions[1::2]):
5195            if isinstance(search, exp.Literal):
5196                ifs.append(exp.If(this=expression.eq(search), true=result))
5197            elif isinstance(search, exp.Null):
5198                ifs.append(exp.If(this=expression.is_(exp.Null()), true=result))
5199            else:
5200                if isinstance(search, exp.Binary):
5201                    search = exp.paren(search)
5202
5203                cond = exp.or_(
5204                    expression.eq(search),
5205                    exp.and_(expression.is_(exp.Null()), search.is_(exp.Null()), copy=False),
5206                    copy=False,
5207                )
5208                ifs.append(exp.If(this=cond, true=result))
5209
5210        case = exp.Case(ifs=ifs, default=expressions[-1] if len(expressions) % 2 == 1 else None)
5211        return self.sql(case)
5212
5213    def semanticview_sql(self, expression: exp.SemanticView) -> str:
5214        this = self.sql(expression, "this")
5215        this = self.seg(this, sep="")
5216        dimensions = self.expressions(
5217            expression, "dimensions", dynamic=True, skip_first=True, skip_last=True
5218        )
5219        dimensions = self.seg(f"DIMENSIONS {dimensions}") if dimensions else ""
5220        metrics = self.expressions(
5221            expression, "metrics", dynamic=True, skip_first=True, skip_last=True
5222        )
5223        metrics = self.seg(f"METRICS {metrics}") if metrics else ""
5224        where = self.sql(expression, "where")
5225        where = self.seg(f"WHERE {where}") if where else ""
5226        return f"SEMANTIC_VIEW({self.indent(this + metrics + dimensions + where)}{self.seg(')', sep='')}"
5227
5228    def getextract_sql(self, expression: exp.GetExtract) -> str:
5229        this = expression.this
5230        expr = expression.expression
5231
5232        if not this.type or not expression.type:
5233            from sqlglot.optimizer.annotate_types import annotate_types
5234
5235            this = annotate_types(this, dialect=self.dialect)
5236
5237        if this.is_type(*(exp.DataType.Type.ARRAY, exp.DataType.Type.MAP)):
5238            return self.sql(exp.Bracket(this=this, expressions=[expr]))
5239
5240        return self.sql(exp.JSONExtract(this=this, expression=self.dialect.to_json_path(expr)))
5241
5242    def datefromunixdate_sql(self, expression: exp.DateFromUnixDate) -> str:
5243        return self.sql(
5244            exp.DateAdd(
5245                this=exp.cast(exp.Literal.string("1970-01-01"), exp.DataType.Type.DATE),
5246                expression=expression.this,
5247                unit=exp.var("DAY"),
5248            )
5249        )
5250
5251    def space_sql(self: Generator, expression: exp.Space) -> str:
5252        return self.sql(exp.Repeat(this=exp.Literal.string(" "), times=expression.this))
5253
5254    def buildproperty_sql(self, expression: exp.BuildProperty) -> str:
5255        return f"BUILD {self.sql(expression, 'this')}"
5256
5257    def refreshtriggerproperty_sql(self, expression: exp.RefreshTriggerProperty) -> str:
5258        method = self.sql(expression, "method")
5259        kind = expression.args.get("kind")
5260        if not kind:
5261            return f"REFRESH {method}"
5262
5263        every = self.sql(expression, "every")
5264        unit = self.sql(expression, "unit")
5265        every = f" EVERY {every} {unit}" if every else ""
5266        starts = self.sql(expression, "starts")
5267        starts = f" STARTS {starts}" if starts else ""
5268
5269        return f"REFRESH {method} ON {kind}{every}{starts}"